pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: limit 1 and functional indexes: SOLVED
2+ messages / 2 participants
[nested] [flat]
* Re: limit 1 and functional indexes: SOLVED
@ 2004-01-30 09:06 Alexandra Birch <alexandra@trymedia.com>
0 siblings, 1 reply; 2+ messages in thread
From: Alexandra Birch @ 2004-01-30 09:06 UTC (permalink / raw)
To: gsstark@mit.edu; Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
> From: gsstark@mit.edu [mailto:gsstark@mit.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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: limit 1 and functional indexes: SOLVED
@ 2004-01-30 09:56 Greg Stark <gsstark@mit.edu>
parent: Alexandra Birch <alexandra@trymedia.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Stark @ 2004-01-30 09:56 UTC (permalink / raw)
To: alexandra@trymedia.com; +Cc: gsstark@mit.edu; Bruno Wolff III <bruno@wolff.to>; pgsql-sql
"Alexandra Birch" <alexandra@trymedia.com> writes:
> 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;
I think what you're trying to do here is get the last order? Then you'll want
the limit to be on the outer query where it's ordered by order_date:
select code,order_date
from (
select code, order_date
from transactions
where UPPER(pop) = UPPER('c892eb2f877e3a28ddc8e196cd5a8aad')
offset 0
) as foo
order by order_date DESC;
limit 1
Note that in theory postgres should be able to find the same plan for this
query as yours since it's equivalent. It really ought to use the order_date
index since it thinks it would be more efficient.
However it's unable to because postgres doesn't try every possible index, only
the ones that look like they'll be useful for a where clause or an order by.
And the order by on the outer query isn't considered when it's looking at the
subquery.
It normally handles this case by merging the subquery into the outer query,
but it can't do that if there's a limit or offset. So an "offset 0" is
convenient for fooling it into thinking the subquery can't be pulled up
without actually changing the output.
You could do "order by upper(pop)" instead which might be clearer for someone
reading the query in that it makes it look like you're trying to encourage it
to use the index on upper(pop). In theory "order by"s on subqueries are
useless and postgres could ignore them, but it doesn't.
--
greg
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-01-30 09:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-30 09:06 Re: limit 1 and functional indexes: SOLVED Alexandra Birch <alexandra@trymedia.com>
2004-01-30 09:56 ` Greg Stark <gsstark@mit.edu>
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