Skip site navigation (1) Skip section navigation (2)

Re: [SQL] limit 1 and functional indexes

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Alexandra Birch <alexandra(at)trymedia(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] limit 1 and functional indexes
Date: 2004-01-29 15:43:19
Message-ID: 20040129154319.GA18840@wolff.to (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
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(at)trymedia(dot)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
> 

In response to

pgsql-performance by date

Next:From: Jack CoatesDate: 2004-01-29 17:23:00
Subject: Re: query optimization question
Previous:From: Bruno Wolff IIIDate: 2004-01-29 15:11:46
Subject: Re: limit 1 and functional indexes

pgsql-sql by date

Next:From: Tom LaneDate: 2004-01-29 15:50:21
Subject: Re: How to retrieve N lines of a text field.
Previous:From: Bruno Wolff IIIDate: 2004-01-29 15:11:46
Subject: Re: limit 1 and functional indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group