On Thu, Feb 17, 2011 at 11:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mark Stosberg <mark@summersault.com> writes:
>> - The query planner didn't like it when the "ORDER BY" referred to a
>> column value instead of a static value, even when I believe it should
>> know that the column value never changes. See this pseudo-query where
>> we look-up the coordinates for 90210 once:
>
>> EXPLAIN ANALYZE
>> SELECT pets.pet_id,
>> zipcodes.lon_lat <-> center.lon_lat AS radius
>> FROM (SELECT lon_lat FROM zipcodes WHERE zipcode = '90210') AS
>> center, pets
>> JOIN shelters USING (shelter_id)
>> JOIN zipcodes USING (zipcode)
>> ORDER BY postal_codes.lon_lat <-> center.lon_lat limit 1000;
>
> As phrased, that's a join condition, so there's no way that an index on
> a single table can possibly satisfy it. You could probably convert it
> to a sub-select though:
>
> ORDER BY postal_codes.lon_lat <-> (SELECT lon_lat FROM zipcodes WHERE zipcode = '90210') limit 1000;
>
> regards, tom lane
Would pushing that subquery to a WITH clause be helpful at all?
