Skip site navigation (1) Skip section navigation (2)

Re: application of KNN code to US zipcode searches?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Stosberg <mark(at)summersault(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: application of KNN code to US zipcode searches?
Date: 2011-02-17 19:17:17
Message-ID: 13093.1297970237@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Oleg BartunovDate: 2011-02-17 20:17:52
Subject: Re: application of KNN code to US zipcode searches?
Previous:From: Tom LaneDate: 2011-02-17 19:13:05
Subject: Re: application of KNN code to US zipcode searches?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group