Re: [SQL] limit 1 and functional indexes - Mailing list pgsql-performance

One other suggestion I forgot is that this should move over to the
performance list rather than being on the sql list. The right people
are more likely to see your question there.

On Thu, Jan 29, 2004 at 16:02:06 +0100,
  Alexandra Birch <alexandra@trymedia.com> wrote:
>
> > >
> > > 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
>