Lists: | pgsql-performance |
---|
From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | best way to fetch next/prev record based on index |
Date: | 2004-07-27 13:07:02 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB34101AEF5@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
I am in a situation where I have to treat a table as logically ordered
based on an index. Right now I'm doing this via queries, and a I need a
better way to do it. Cursors do not meet my requirements, because they
are always insensitive. Also, my performance requirements are
extreme...I need 100% index usage.
Currently, I use queries to do this. Unfortunately, the queries can get
kind of complex because many if the indexes (keys, really) are over 3 or
more columns in a table.
So, for a table t with a three part key over columns a,b,c, the query to
read the next value from t for given values a1, b1, c1 is
select * from t where
a >= a1 and
(a > a1 or b >= b1) and
(a > a1 or b > b1 or c > c1)
In about 95% of cases, the planner correctly selects the index t(a,b,c)
and uses it. However, the 5% remaining cases usually come at the worst
time, when large tables and 3 or 4 part keys are involved. In those
cases sometimes the planner applies the filter to a, but not b or c with
a large performance hit. Manipulating statistics on the table does not
seem to help.
Interestingly, it is possible to rewrite the above query by switching
and with or and >= with >. However when written that way, the planner
almost never gets it right.
My problem is deceptively simple: how you read the next record from a
table based on a given set of values? In practice, this is difficult to
implement. If anybody can suggest a alternative/better way to this, I'm
all ears.
Merlin
From: | Markus Schaber <schabios(at)logi-track(dot)com> |
---|---|
To: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: best way to fetch next/prev record based on index |
Date: | 2004-07-27 14:13:25 |
Message-ID: | 20040727161325.213b11eb@kingfisher.intern.logi-track.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Hi, Merlin,
On Tue, 27 Jul 2004 09:07:02 -0400
"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> wrote:
> So, for a table t with a three part key over columns a,b,c, the query
> to read the next value from t for given values a1, b1, c1 is
>
> select * from t where
> a >= a1 and
> (a > a1 or b >= b1) and
> (a > a1 or b > b1 or c > c1)
You mut not rely on such trickery to get any ordering, as the SQL data
model contains no ordering, and a query optimizer is free to deliver you
the tuples in any order it feels like.
Why don't you add a 'ORDER BY a,b,c ASC' to your query?
> Interestingly, it is possible to rewrite the above query by switching
> and with or and >= with >. However when written that way, the planner
> almost never gets it right.
That's the reason why you cannot rely on any implicit ordering, the
planner is free to rewrite a query as it likes as long as it delivers
the same tuples, but in any order it wants.
> My problem is deceptively simple: how you read the next record from a
> table based on a given set of values? In practice, this is difficult
> to implement. If anybody can suggest a alternative/better way to
> this, I'm all ears.
So you really want something like
'SELECT * FROM t WHERE a>=a1 AND b>=b1 AND c>=c1 ORDER BY a,b,c ASC LIMIT 1'
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com
From: | Markus Schaber <schabios(at)logi-track(dot)com> |
---|---|
To: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Correction of best way to fetch next/prev record based on index |
Date: | 2004-07-27 14:21:17 |
Message-ID: | 20040727162117.6de666b6@kingfisher.intern.logi-track.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Hi, Merlin,
On Tue, 27 Jul 2004 16:13:25 +0200, I myself wrote:
> You mut not
Should be "must", not "mut" :-)
> > My problem is deceptively simple: how you read the next record from
> > a table based on a given set of values? In practice, this is
> > difficult to implement. If anybody can suggest a alternative/better
> > way to this, I'm all ears.
>
> So you really want something like
>
> 'SELECT * FROM t WHERE a>=a1 AND b>=b1 AND c>=c1 ORDER BY a,b,c ASC
> LIMIT 1'
Sorry, as you want the _next_, and I assume that a1, b1 and c1 are the
current row's values, you should rather use something like:
'SELECT * FROM t WHERE a>=a1 AND b>=b1 AND c>=c1 ORDER BY a,b,c ASC
LIMIT 1 OFFSET 1'
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com
From: | Rod Taylor <pg(at)rbt(dot)ca> |
---|---|
To: | Merlin Moncure <merlin(dot)moncure(at)rcsonline(dot)com> |
Cc: | Postgresql Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: best way to fetch next/prev record based on index |
Date: | 2004-07-27 14:36:34 |
Message-ID: | 1090938993.83536.85.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
You only want one record to be returned? Tack a LIMIT 1 onto the end of
the query.
> My problem is deceptively simple: how you read the next record from a
> table based on a given set of values? In practice, this is difficult to
> implement. If anybody can suggest a alternative/better way to this, I'm
> all ears.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: best way to fetch next/prev record based on index |
Date: | 2004-07-27 15:14:01 |
Message-ID: | 28700.1090941241@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes:
> So, for a table t with a three part key over columns a,b,c, the query to
> read the next value from t for given values a1, b1, c1 is
> select * from t where
> a >= a1 and
> (a > a1 or b >= b1) and
> (a > a1 or b > b1 or c > c1)
> In about 95% of cases, the planner correctly selects the index t(a,b,c)
> and uses it.
I'm surprised it's that good. Why not do
select * from t where a >= a1 and b >= b1 and c >= c1
order by a,b,c
limit 1 offset 1;
which has a much more obvious translation to an indexscan.
regards, tom lane
From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: best way to fetch next/prev record based on index |
Date: | 2004-07-27 17:12:31 |
Message-ID: | 876589v0g0.fsf@stark.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
> Interestingly, it is possible to rewrite the above query by switching
> and with or and >= with >. However when written that way, the planner
> almost never gets it right.
Well, note it's still not really getting it right even in your case. It's
doing an index scan on a>=a1 but if you have lots of values in your table
where a=a1 and b<b1 then it's going to unnecessarily read through all of
those.
One thing that can help is to add ORDER BY a,b,c LIMIT 1 to your query. That
will virtually guarantee that it uses an index scan, which will at least avoid
making it scan all the records *after* finding the match. However it still
doesn't seem to make Postgres use an Index Cond to allow it to do an instant
lookup.
I expected WHERE (a,b,c) > (a1,b1,c1) to work however it doesn't. It appears
to mean a>a1 AND b>b1 AND c>c1 which isn't at all what you want. I imagine the
standard dictates this meaning.
> My problem is deceptively simple: how you read the next record from a
> table based on a given set of values? In practice, this is difficult to
> implement. If anybody can suggest a alternative/better way to this, I'm
> all ears.
I've done this a million times for simple integer keys, but I've never had to
do it for multi-column keys. It seems it would be nice if some syntax similar
to (a,b,c) > (a1,b1,c1) worked for this.
--
greg