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

Re: limit 1 and functional indexes

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Alexandra Birch <alexandra(at)trymedia(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: limit 1 and functional indexes
Date: 2004-01-29 15:11:46
Message-ID: 20040129151146.GB6865@wolff.to (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
On Thu, Jan 29, 2004 at 16:02:06 +0100,
  Alexandra Birch <alexandra(at)trymedia(dot)com> wrote:
> 
> Here is the output of EXPLAIN ANALYZE first with limit 1 then without:

The time estimate for the limit 1 case is way off. I can't tell if that
is a bug or not having detailed enough statistics.

Hopefully someone more knowlegable will take a look at this question.

> 
> 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

Responses

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2004-01-29 15:43:19
Subject: Re: [SQL] limit 1 and functional indexes
Previous:From: Alexandra BirchDate: 2004-01-29 15:02:06
Subject: Re: limit 1 and functional indexes

pgsql-sql by date

Next:From: Bruno Wolff IIIDate: 2004-01-29 15:43:19
Subject: Re: [SQL] limit 1 and functional indexes
Previous:From: Alexandra BirchDate: 2004-01-29 15:02:06
Subject: Re: limit 1 and functional indexes

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