Thread: query not using index for descending records?
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_idon 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
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.
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
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.
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
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.
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
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.
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
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