On Thu, Feb 17, 2011 at 11:17 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Mark Stosberg <mark@xxxxxxxxxxxxxxx> 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? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
