X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4BC80D1B481 for ; Thu, 29 Jan 2004 13:17:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 19551-10 for ; Thu, 29 Jan 2004 09:17:32 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 66FD1D1D17E for ; Thu, 29 Jan 2004 09:17:21 -0400 (AST) Received: (qmail 8847 invoked by uid 500); 29 Jan 2004 13:18:47 -0000 Date: Thu, 29 Jan 2004 07:18:47 -0600 From: Bruno Wolff III To: email lists Cc: pgsql-sql@postgresql.org Subject: Re: query not using index for descending records? Message-ID: <20040129131847.GE17068@wolff.to> Mail-Followup-To: email lists , pgsql-sql@postgresql.org References: <035C9F7CE28601428BBB5B051C9F77F2017898@orion> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <035C9F7CE28601428BBB5B051C9F77F2017898@orion> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/256 X-Sequence-Number: 16649 On Thu, Jan 29, 2004 at 22:18:08 +1000, email lists 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.