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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
