Re: limit 1 and functional indexes: SOLVED

> From: gsstark(at)mit(dot)edu [mailto:gsstark(at)mit(dot)edu]
> Sent: viernes, 30 de enero de 2004 7:08
>
> 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.

Thanks for the explication.

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

That would be great but we store a variety of case insensitive proof of purchase
codes here. Some we give to customers in upper case and some in lower case.
Hopefully someday we can redesign it all to just be in uppercase...

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

It works perfectly - thanks a million!
Strangely the offset 0 does not seem to make any difference.
Gotta read up more about subqueries :)

explain analyze
select code,order_date
from (
select code, order_date
from transactions
where UPPER(pop) = UPPER('c892eb2f877e3a28ddc8e196cd5a8aad')
limit 1
) as foo
order by order_date DESC;
--------------------------------------------------
Sort (cost=3.95..3.96 rows=1 width=33) (actual time=0.14..0.14 rows=1 loops=1)
Sort Key: order_date
-> Subquery Scan foo (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.07 rows=1 loops=1)
-> Limit (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.06 rows=1 loops=1)
-> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.84 rows=2956 width=33) (actual
time=0.05..0.06 rows=2 loops=1)
Index Cond: (upper((pop)::text) = 'C892EB2F877E3A28DDC8E196CD5A8AAD'::text)
Total runtime: 0.20 msec
(7 rows)

explain analyze
select code,order_date
from (
select code, order_date
from transactions
where UPPER(pop) = UPPER('c892eb2f877e3a28ddc8e196cd5a8aad')
limit 1
offset 0
) as foo
order by order_date DESC;
--------------------------------------------------
Sort (cost=3.95..3.96 rows=1 width=33) (actual time=0.14..0.14 rows=1 loops=1)
Sort Key: order_date
-> Subquery Scan foo (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.07 rows=1 loops=1)
-> Limit (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.06 rows=1 loops=1)
-> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.84 rows=2956 width=33) (actual
time=0.06..0.06 rows=2 loops=1)
Index Cond: (upper((pop)::text) = 'C892EB2F877E3A28DDC8E196CD5A8AAD'::text)
Total runtime: 0.20 msec