From: Bruno Wolff III <bruno@wolff.to>
To: Alexandra Birch <alexandra@trymedia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: limit 1 and functional indexes
Date: Thu, 29 Jan 2004 06:52:40 -0600
Message-ID: <20040129125240.GA17068@wolff.to> (raw)
In-Reply-To: <BJELKOAELOIHMLJIEGHJGENNENAA.alexandra@trymedia.com>
References: <BJELKOAELOIHMLJIEGHJGENNENAA.alexandra@trymedia.com>
On Wed, Jan 28, 2004 at 12:23:38 +0100,
Alexandra Birch <alexandra@trymedia.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, alexandra@trymedia.com
Subject: Re: limit 1 and functional indexes
In-Reply-To: <20040129125240.GA17068@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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