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 09:11:46 -0600
Message-ID: <20040129151146.GB6865@wolff.to> (raw)
In-Reply-To: <BJELKOAELOIHMLJIEGHJOEAAEOAA.alexandra@trymedia.com>
References: <20040129125240.GA17068@wolff.to>
<BJELKOAELOIHMLJIEGHJOEAAEOAA.alexandra@trymedia.com>
On Thu, Jan 29, 2004 at 16:02:06 +0100,
Alexandra Birch <alexandra@trymedia.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
>
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: <20040129151146.GB6865@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