From: Greg Stark <gsstark@mit.edu>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Alexandra Birch <alexandra@trymedia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: limit 1 and functional indexes
Date: 30 Jan 2004 01:07:39 -0500
Message-ID: <874queyno4.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20040129151146.GB6865@wolff.to>
References: <20040129125240.GA17068@wolff.to>
<BJELKOAELOIHMLJIEGHJOEAAEOAA.alexandra@trymedia.com>
<20040129151146.GB6865@wolff.to>
Bruno Wolff III <bruno@wolff.to> writes:
> > 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
Yeah, the problem with functional indexes is that the optimizer doesn't have
any clue how the records are distributed since it only has statistics for
columns, not your expression. Notice it's estimating 2956 rows where in fact
there are 0.
I think someone was actually working on this so it may be improved in 7.5 but
I'm not sure.
Given the type of data you're storing, which looks like hex strings, are you
sure you need to do a case-insensitive search here? Can't you just uppercase
it when you store it?
The other option would be to use a subquery and force the planner not to pull
it up, something like:
select code
from (
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
offset 0
)
order by order_date DESC;
The offset 0 prevents the optimizer from pulling the subquery into the outer
query. I think this will prevent it from even considering the order_date index
scan, but you'll have to try to be sure.
--
greg
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: gsstark@mit.edu, bruno@wolff.to, alexandra@trymedia.com
Subject: Re: limit 1 and functional indexes
In-Reply-To: <874queyno4.fsf@stark.xeocode.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