pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery not using index for descending records?
10+ messages / 5 participants
[nested] [flat]
* query not using index for descending records?
@ 2004-01-29 12:18 email lists <lists@darrenmackay.com>
0 siblings, 1 reply; 10+ messages in thread
From: email lists @ 2004-01-29 12:18 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I have this table;
id - Serial
datetime - timestamp without timezone
with the index as
index idx_trafficlogs_datetime_id on trafficlogs using btree
(datetime,id);
When performing the following query:
explain select datetime,id from trafficlogs order by datetime,id limit
20;
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------
Limit (cost=0.00..2.31 rows=20 width=12)
-> Index Scan using idx_trafficlogs_datetime_id on trafficlogs
(cost=0.00..1057.89 rows=9172 width=12)
(2 rows)
however, I am wanting to return the last 20 records. I have been using:
explain select datetime,id from trafficlogs order by datetime,id desc
limit 20;
QUERY PLAN
------------------------------------------------------------------------
---------
Limit (cost=926.38..926.43 rows=20 width=12)
-> Sort (cost=926.38..949.31 rows=9172 width=12)
Sort Key: datetime, id
-> Seq Scan on trafficlogs (cost=0.00..322.72 rows=9172
width=12)
as you can see, a sequential scan is performed.
How do I get pg to use an index scan for this query. The table in a
production environment grows by approx 150,000 records daily, hence long
term performance is a major factor here - for each additional day of
data, the above query takes an additional 6-8 secs to run.
Tia,
Darren
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 13:18 Bruno Wolff III <bruno@wolff.to>
parent: email lists <lists@darrenmackay.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2004-01-29 13:18 UTC (permalink / raw)
To: email lists <lists@darrenmackay.com>; +Cc: pgsql-sql
On Thu, Jan 29, 2004 at 22:18:08 +1000,
email lists <lists@darrenmackay.com> wrote:
> Limit (cost=0.00..2.31 rows=20 width=12)
> -> Index Scan using idx_trafficlogs_datetime_id on trafficlogs
> (cost=0.00..1057.89 rows=9172 width=12)
> (2 rows)
>
> however, I am wanting to return the last 20 records. I have been using:
>
> explain select datetime,id from trafficlogs order by datetime,id desc
> limit 20;
You probably don't want to do that. The DESC only applies to the one
expression it follows. What you want is probably:
explain select datetime,id from trafficlogs order by datetime desc,id desc
limit 20;
The index won't get used because with id and datetime being checked in
different orders, only the first part of the index is usable. And probably
that wasn't selective enough for an index scan to be used.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 13:29 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleus Mantzios @ 2004-01-29 13:29 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: email lists <lists@darrenmackay.com>; pgsql-sql
O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 :
As i see there was a thread
http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php
dealing with this issue, assuming the "correct" order by
should be "order by datetime ASC, id DESC".
Do you know of any progress for declaring the direction of each
column in a multicolumn index?
> On Thu, Jan 29, 2004 at 22:18:08 +1000,
> email lists <lists@darrenmackay.com> wrote:
> > Limit (cost=0.00..2.31 rows=20 width=12)
> > -> Index Scan using idx_trafficlogs_datetime_id on trafficlogs
> > (cost=0.00..1057.89 rows=9172 width=12)
> > (2 rows)
> >
> > however, I am wanting to return the last 20 records. I have been using:
> >
> > explain select datetime,id from trafficlogs order by datetime,id desc
> > limit 20;
>
> You probably don't want to do that. The DESC only applies to the one
> expression it follows. What you want is probably:
> explain select datetime,id from trafficlogs order by datetime desc,id desc
> limit 20;
>
> The index won't get used because with id and datetime being checked in
> different orders, only the first part of the index is usable. And probably
> that wasn't selective enough for an index scan to be used.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
-Achilleus
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 14:06 Bruno Wolff III <bruno@wolff.to>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 10+ messages in thread
From: Bruno Wolff III @ 2004-01-29 14:06 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: email lists <lists@darrenmackay.com>; pgsql-sql
On Thu, Jan 29, 2004 at 15:29:11 +0200,
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 :
>
> As i see there was a thread
> http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php
> dealing with this issue, assuming the "correct" order by
> should be "order by datetime ASC, id DESC".
So you really didn't want them in the reverse order?
> Do you know of any progress for declaring the direction of each
> column in a multicolumn index?
If you are using 7.4 you can use a functional index to get around this.
Assuming id is a numeric type, you can make an index on datetime and
(-id) and sort by datetime, -id and the index should get used.
This should still get fixed at some point, as this trick doesn't work
for types for which the - operator exists. But I haven't heard of
anyone working on it for 7.5, so don't expect a real fix any time soon.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 15:01 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 10+ messages in thread
From: Achilleus Mantzios @ 2004-01-29 15:01 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: email lists <lists@darrenmackay.com>; pgsql-sql
O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 :
> On Thu, Jan 29, 2004 at 15:29:11 +0200,
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 :
> >
> > As i see there was a thread
> > http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php
> > dealing with this issue, assuming the "correct" order by
> > should be "order by datetime ASC, id DESC".
>
> So you really didn't want them in the reverse order?
I am not the initiator of this thread,
i was just lurking :)
>
> > Do you know of any progress for declaring the direction of each
> > column in a multicolumn index?
>
> If you are using 7.4 you can use a functional index to get around this.
> Assuming id is a numeric type, you can make an index on datetime and
> (-id) and sort by datetime, -id and the index should get used.
>
> This should still get fixed at some point, as this trick doesn't work
> for types for which the - operator exists. But I haven't heard of
> anyone working on it for 7.5, so don't expect a real fix any time soon.
>
It would be nice to have this feature for varchar as well.
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
-Achilleus
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 16:05 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2004-01-29 16:05 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; email lists <lists@darrenmackay.com>; pgsql-sql
On Thu, 29 Jan 2004, Bruno Wolff III wrote:
> On Thu, Jan 29, 2004 at 15:29:11 +0200,
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 :
> >
> > As i see there was a thread
> > http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php
> > dealing with this issue, assuming the "correct" order by
> > should be "order by datetime ASC, id DESC".
>
> So you really didn't want them in the reverse order?
>
> > Do you know of any progress for declaring the direction of each
> > column in a multicolumn index?
>
> If you are using 7.4 you can use a functional index to get around this.
> Assuming id is a numeric type, you can make an index on datetime and
> (-id) and sort by datetime, -id and the index should get used.
>
> This should still get fixed at some point, as this trick doesn't work
> for types for which the - operator exists. But I haven't heard of
> anyone working on it for 7.5, so don't expect a real fix any time soon.
I'd thought that I'd previously sent a message containing a set of
definitions for the reverse opclasses (not meant for inclusion to the
system because I was making SQL functions that basically did -<normal
comparison function> to use as the function of the operator class, but
possibly worth playing with) but now that I actually search again, I don't
see it.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 17:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2004-01-29 17:24 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; email lists <lists@darrenmackay.com>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I'd thought that I'd previously sent a message containing a set of
> definitions for the reverse opclasses (not meant for inclusion to the
> system because I was making SQL functions that basically did -<normal
> comparison function> to use as the function of the operator class, but
> possibly worth playing with) but now that I actually search again, I don't
> see it.
I don't recall having seen such a thing go by...
I fear that using SQL functions as comparators would only be useful for
proof-of-concept, not as an industrial-strength implementation. The
index code needs comparator functions not to leak memory, and I doubt
that that could be guaranteed with a SQL function. You'd probably have
speed issues too.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 18:27 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2004-01-29 18:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruno Wolff III <bruno@wolff.to>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; email lists <lists@darrenmackay.com>; pgsql-sql
On Thu, 29 Jan 2004, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > I'd thought that I'd previously sent a message containing a set of
> > definitions for the reverse opclasses (not meant for inclusion to the
> > system because I was making SQL functions that basically did -<normal
> > comparison function> to use as the function of the operator class, but
> > possibly worth playing with) but now that I actually search again, I don't
> > see it.
>
> I don't recall having seen such a thing go by...
>
> I fear that using SQL functions as comparators would only be useful for
> proof-of-concept, not as an industrial-strength implementation. The
> index code needs comparator functions not to leak memory, and I doubt
> that that could be guaranteed with a SQL function. You'd probably have
> speed issues too.
Yeah, that's what I figured. I thought it might be useful for people to
play with though since at least for the integer/float types writing C
versions of the comparitors is easy. I was thinking for real it'd be nice
to be able to use the normal comparitor but invert the return value as
necessary rather than providing two functions, but I didn't look at what
that would take.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-29 18:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2004-01-29 18:40 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; email lists <lists@darrenmackay.com>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Yeah, that's what I figured. I thought it might be useful for people to
> play with though since at least for the integer/float types writing C
> versions of the comparitors is easy. I was thinking for real it'd be nice
> to be able to use the normal comparitor but invert the return value as
> necessary rather than providing two functions, but I didn't look at what
> that would take.
I think the C versions should be written to just call the "normal"
comparators and negate the result, which'll make them one-liner
boilerplate. It's just a matter of grinding out all that boilerplate ...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query not using index for descending records?
@ 2004-01-31 05:09 email lists <lists@darrenmackay.com>
0 siblings, 0 replies; 10+ messages in thread
From: email lists @ 2004-01-31 05:09 UTC (permalink / raw)
To: pgsql-sql
Hi,
| You probably don't want to do that. The DESC only applies to the
| one expression it follows. What you want is probably:
| explain select datetime,id from trafficlogs order by
| datetime desc,id desc limit 20;
This is exactly what I was after - worked a treat!
Thanks.
Darren
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-01-31 05:09 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-29 12:18 query not using index for descending records? email lists <lists@darrenmackay.com>
2004-01-29 13:18 ` Bruno Wolff III <bruno@wolff.to>
2004-01-29 13:29 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-01-29 14:06 ` Bruno Wolff III <bruno@wolff.to>
2004-01-29 15:01 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-01-29 16:05 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-29 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-29 18:27 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-29 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-31 05:09 Re: query not using index for descending records? email lists <lists@darrenmackay.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox