Thread: 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