From: Alexandra Birch <alexandra@trymedia.com>
To: bruno@wolff.to
Cc: pgsql-sql@postgresql.org
Subject: Re: limit 1 and functional indexes
Date: Thu, 29 Jan 2004 16:02:06 +0100
Message-ID: <BJELKOAELOIHMLJIEGHJOEAAEOAA.alexandra@trymedia.com> (raw)
In-Reply-To: <20040129125240.GA17068@wolff.to>
> >
> > 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.
Thanks - your reply is apreciated :)
> It IS reasobable for the planner to choose a different plan when you
> add a LIMIT clause to a query.
OK - I'll investigate this further.
> > 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.
OK - but there was some discussion about statistics for functional indexes, for eg:
http://archives.postgresql.org/pgsql-general/2004-01/msg00978.php
This does not help me solve my problem though :)
> > 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?
Yip - I should have said we do a daily VACUUM ANALYZE.
> 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.
I thought the cost values would be enough from the EXPLAIN alone.
And the query takes so long to run :(
Here is the output of EXPLAIN ANALYZE first with limit 1 then without:
explain analyze
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC LIMIT 1;
--------------------------------------------------------------------------------------------------
Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0 loops=1)
-> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..982549.96 rows=2956 width=33) (actual
time=377718.61..377718.61 rows=0 loops=1)
Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
Total runtime: 378439.32 msec
explain analyze
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
-------------
Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0 loops=1)
Sort Key: order_date
-> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79 rows=2956 width=33) (actual time=126.13..126.13
rows=0 loops=1)
Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
Total runtime: 248.25 msec
Thank you,
Alexandra
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: alexandra@trymedia.com, bruno@wolff.to
Subject: Re: limit 1 and functional indexes
In-Reply-To: <BJELKOAELOIHMLJIEGHJOEAAEOAA.alexandra@trymedia.com>
* 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