From: Greg Stark <gsstark@mit.edu>
To: alexandra@trymedia.com
Cc: gsstark@mit.edu
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: limit 1 and functional indexes: SOLVED
Date: 30 Jan 2004 04:56:22 -0500
Message-ID: <87smhxyd2x.fsf@stark.xeocode.com> (raw)
In-Reply-To: <BJELKOAELOIHMLJIEGHJMEBBEOAA.alexandra@trymedia.com>
References: <BJELKOAELOIHMLJIEGHJMEBBEOAA.alexandra@trymedia.com>
"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
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, alexandra@trymedia.com, bruno@wolff.to
Subject: Re: limit 1 and functional indexes: SOLVED
In-Reply-To: <87smhxyd2x.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