Lists: | pgsql-hackers |
---|
From: | Markus Wanner <markus(at)bluegap(dot)ch> |
---|---|
To: | PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Index scan troubles |
Date: | 2008-09-02 16:09:17 |
Message-ID: | 48BD652D.9060303@bluegap.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | Markus Wanner <markus(at)bluegap(dot)ch> |
Cc: | PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Index scan troubles |
Date: | 2008-09-02 17:17:10 |
Message-ID: | 87y72aqxgp.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Markus Wanner <markus(at)bluegap(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Markus Wanner <markus(at)bluegap(dot)ch> |
Cc: | PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Index scan troubles |
Date: | 2008-09-02 18:03:39 |
Message-ID: | 16843.1220378619@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Markus Wanner <markus(at)bluegap(dot)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
From: | Markus Wanner <markus(at)bluegap(dot)ch> |
---|---|
To: | Gregory Stark <stark(at)enterprisedb(dot)com> |
Cc: | PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Index scan troubles |
Date: | 2008-09-02 18:57:38 |
Message-ID: | 48BD8CA2.7050204@bluegap.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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