To: Mark Stosberg <mark@summersault.com>
cc: pgsql-performance@postgresql.org
Subject: Re: application of KNN code to US zipcode searches? 
In-reply-to: <ijjj4g$c2k$1@dough.gmane.org> 
References: <ijjc1o$vgu$1@dough.gmane.org>
	<4D5CE118020000250003AB83@gw.wicourts.gov>
	<ijjecs$e8c$1@dough.gmane.org>
	<20110217152445.GV4116@tamriel.snowman.net>
	<ijjge9$qtt$1@dough.gmane.org> <ijjj4g$c2k$1@dough.gmane.org>
Comments: In-reply-to Mark Stosberg <mark@summersault.com>
	message dated "Thu, 17 Feb 2011 11:41:51 -0500"
Date: Thu, 17 Feb 2011 14:17:17 -0500
Message-ID: <13093.1297970237@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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