Обсуждение: Index scan troubles
Hi, I'm having a hard time using an index scan. So far, I've done quite well with ScanKeyInit for equality searches. But now I need to scan an index from a given starting point. Something like: (x, y, z,...) > (const, const, const,...) For the equality operatior, I've used get_sort_group_operators() in combination with get_opcode() to pass that on as the sk_func of the scan key. I slowly begin to doubt if that's correct at all. While it works for equality scans, it does somehow not work for for BTGreaterStrategy. What am I missing? I do have the following: an indexStruct on the index (primary key) I want to use, a TupleDescriptor for the relation I want tuples from and of course the list of constants (datums) to compare against. I want to do an index scan to retrieve tuples from that given lower bound upwards (or forwards). I don't quite grok all the opfamily and opclass things, yet. Hints and pointers on where to read on greatly appreciated. A virtual beer for sample code. ;-) Regards Markus Wanner
Markus Wanner <markus@bluegap.ch> writes: > Hi, > > I'm having a hard time using an index scan. So far, I've done quite well with > ScanKeyInit for equality searches. But now I need to scan an index from a given > starting point. Something like: > > (x, y, z,...) > (const, const, const,...) > > For the equality operatior, I've used get_sort_group_operators() in combination > with get_opcode() to pass that on as the sk_func of the scan key. I slowly > begin to doubt if that's correct at all. It's right for your equality case which is effectively x=const, y=const, z=const. It's not for row comparisons case for which you need a funny "header" ScanKey. See the comments in access/skey.h, search for "row comparisons". I'm not sure if there's a function to create this for you or if you have to do it yourself. Search for other places where SK_ROW_HEADER appears. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
Markus Wanner <markus@bluegap.ch> writes: > I'm having a hard time using an index scan. So far, I've done quite well > with ScanKeyInit for equality searches. But now I need to scan an index > from a given starting point. Something like: > (x, y, z,...) > (const, const, const,...) > For the equality operatior, I've used get_sort_group_operators() in > combination with get_opcode() to pass that on as the sk_func of the scan > key. I slowly begin to doubt if that's correct at all. > While it works for equality scans, it does somehow not work for for > BTGreaterStrategy. What am I missing? A row comparison (a,b,c) > (x,y,z) means something entirely different from a>x AND b>y AND c>z; but it sounds like the keys you are creating define the latter condition. Look at the RowCompareExpr stuff in nodeIndexscan.c to see how to build a scankey that means the former. regards, tom lane
Hi, Gregory Stark wrote: > It's right for your equality case which is effectively x=const, y=const, > z=const. It's not for row comparisons case for which you need a funny "header" > ScanKey. See the comments in access/skey.h, search for "row comparisons". I'm > not sure if there's a function to create this for you or if you have to do it > yourself. Search for other places where SK_ROW_HEADER appears. Ah, so the default for multiple ScanKeys is 'x > const AND y > const' and not the row comparison. That explains my troubles. Thanks a lot. Regards Markus Wanner