Re: application of KNN code to US zipcode searches?

On Thu, Feb 17, 2011 at 11:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Mark Stosberg <mark(at)summersault(dot)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?