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 397DBD1B484 for ; Thu, 29 Jan 2004 12:51:48 +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 10174-02 for ; Thu, 29 Jan 2004 08:51:25 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id D9A4FD1D249 for ; Thu, 29 Jan 2004 08:51:15 -0400 (AST) Received: (qmail 28220 invoked by uid 500); 29 Jan 2004 12:52:40 -0000 Date: Thu, 29 Jan 2004 06:52:40 -0600 From: Bruno Wolff III To: Alexandra Birch Cc: pgsql-sql@postgresql.org Subject: Re: limit 1 and functional indexes Message-ID: <20040129125240.GA17068@wolff.to> Mail-Followup-To: Alexandra Birch , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/251 X-Sequence-Number: 16644 On Wed, Jan 28, 2004 at 12:23:38 +0100, Alexandra Birch wrote: > Hi, > > Postgres choses the wrong index when I add limit 1 to the query. > This should not affect the index chosen. I don't know the complete answer to your question, but since no one else has commented I will answer what I can. It IS reasobable for the planner to choose a different plan when you add a LIMIT clause to a query. > I read that functional indexes are sometimes not chosen correctly by > optimizer. I don't believe there are any particular problems with functional indexes. The opitmizer isn't perfect and will sometimes choose poor plans. > Is there anything I can do to always use the functional index in the > following queries? Have you done an ANALYZE of the table recently? It might be useful to see the EXPLAIN ANALYZE output, rather than just the EXPLAIN output, as that will give the actual times needed to do the various steps. > > Query with limit 1 choses wrong index: > --------------------------------------------------------------------------------------- > explain > select code > from transactions > where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111') > order by order_date DESC LIMIT 1 > > Index Scan Backward using transactions_date_aff on transactions (cost=0.00..930780.96 rows=2879 width=33) > --------------------------------------------------------------------------------------- > > Without limit 1 choses correct index: > --------------------------------------------------------------------------------------- > explain > select code > from transactions > where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111') > order by order_date DESC > > Index Scan using transactions_pop_i on transactions (cost=0.00..11351.72 rows=2879 width=33) > --------------------------------------------------------------------------------------- > > We have postgresql-7.3.2-3. > Thank you, > > Alexandra > > ---------------------------(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