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