To: <alexandra@trymedia.com>
Cc: <gsstark@mit.edu>, "Bruno Wolff III" <bruno@wolff.to>,
	<pgsql-sql@postgresql.org>
Subject: Re: limit 1 and functional indexes: SOLVED
References: <BJELKOAELOIHMLJIEGHJMEBBEOAA.alexandra@trymedia.com>
In-Reply-To: <BJELKOAELOIHMLJIEGHJMEBBEOAA.alexandra@trymedia.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 30 Jan 2004 04:56:22 -0500
Message-ID: <87smhxyd2x.fsf@stark.xeocode.com>
Lines: 51
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"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