Re: limit 1 and functional indexes

Bruno Wolff III <bruno(at)wolff(dot)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