Re: best way to fetch next/prev record based on index - Mailing list pgsql-performance

From Merlin Moncure
Subject Re: best way to fetch next/prev record based on index
Date
Msg-id 6EE64EF3AB31D5448D0007DD34EEB34101AF03@Herge.rcsinc.local
Whole thread Raw
In response to best way to fetch next/prev record based on index  ("Merlin Moncure" <merlin.moncure@rcsonline.com>)
List pgsql-performance
> Greg Stark <gsstark@MIT.EDU> writes:
> This approach won't get the optimizer to actually use an index for
these
> comparisons, but it will fix the semantics to match the spec. Later we
can
> either improve the optimizer to detect expressions like this (which I
> think
> would be cooler since some users may write them by hand and not use
the
> row-expression approach, but I don't see how to do it), or introduce a
new
> run-time node and have the optimizer handle it. But at least we won't
have
> to
> worry about backwards-compatibility issues with the semantics
changing.
>
> Oh, I tried to stick to the style, but sometimes I couldn't help
myself. I
> suppose I would have to fix up the style the rest of the way if I got
it
> working and you wanted a patch to apply.

Regarding the <= and >= operators:  can you apply them in the complex
pass? If you can, this might be more efficient.

> /*
>  * Handles something like:
>  * (A,B,C) > (X,Y,Z)
>  *
>  * By constructing something like:
>  * ( ( A > X) OR (A=X AND B>Y) OR (A=X AND B=Y AND C>Z) )
>  *                                                  ^
>  */                                                 |

the last comparison of the last major clause (or the only comparison for
a single field row construct) is a special case.  In > cases use >, in
>= cases use >=, etc.; this is logical equivalent to doing or of simple
= intersected with complex >.

Is this step of the transformation visible to the optimizer/planner?
For purely selfish reasons, it would be really nice if a field by field
row construction could get a fast path to the index if the fields match
the index fields.

Merlin

pgsql-performance by date:

Previous
From: Greg Stark
Date:
Subject: Re: best way to fetch next/prev record based on index
Next
From: Tom Lane
Date:
Subject: Re: best way to fetch next/prev record based on index