To: Bruno Wolff III <bruno@wolff.to>
Cc: Alexandra Birch <alexandra@trymedia.com>,
	pgsql-sql@postgresql.org
Subject: Re: limit 1 and functional indexes
References: <20040129125240.GA17068@wolff.to>
	<BJELKOAELOIHMLJIEGHJOEAAEOAA.alexandra@trymedia.com>
	<20040129151146.GB6865@wolff.to>
In-Reply-To: <20040129151146.GB6865@wolff.to>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 30 Jan 2004 01:07:39 -0500
Message-ID: <874queyno4.fsf@stark.xeocode.com>
Lines: 46
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


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