Re: cube operations slower than geo_distance() on production server

On Tue, Feb 13, 2007 at 09:31:18AM -0500, Merlin Moncure wrote:
>
> >my mistake, i misunderstood what you were trying to do...can you try
> >removing the 'order by radius' and see if it helps? if not, we can try
> >working on this query some more. There is a better, faster way to do
> >this, I'm sure of it.

Merlin,

Thanks again for your help. I did try without the "order by", and it
didn't make more difference.

> try this:

Based on your example, I was able to further refine the query to remove
the duplicate sub-selects that I had. However, this didn't seem to
improve performance.

I'm still stuck with the same essential problem: On the development
server, where is less data (400 results returns vs 1300), the cube
search is at least twice as fast, but on the production server, it is
consistently slower.

So, either the difference is one of scale, or I have some different
configuration detail in production that is causing the issue.

For reference, here's two versions of the query. The first uses
the old geo_distance(), and the second one is the new cube query I'm
trying, inspired by your suggested refactoring.

It's not surprising to me that the queries run at different speeds
on different servers, but it /is/ surprising that their relative speeds
reverse!

Mark

-- Searching for all dogs within 10 miles of 90210 zipcode
EXPLAIN ANALYZE
SELECT
zipcodes.lon_lat <@> center.lon_lat AS radius
FROM (SELECT lon_lat FROM zipcodes WHERE zipcode = '90210') as center,
pets
JOIN shelters_active as shelters USING (shelter_id)
JOIN zipcodes on (shelters.postal_code_for_joining = zipcodes.zipcode)
WHERE species_id = 1
AND pet_state='available'
AND (zipcodes.lon_lat <@> center.lon_lat) < 10
ORDER BY RADIUS;

EXPLAIN ANALYZE
SELECT
cube_distance( center.earth_coords , zipcodes.earth_coords)/1609.344
AS RADIUS
FROM (SELECT
earth_coords,
earth_box( earth_coords , 10*1609.344 ) as center_box
from zipcodes WHERE zipcode = '90210'
) AS center,
pets
JOIN shelters_active AS shelters USING (shelter_id)
JOIN zipcodes ON ( shelters.postal_code_for_joining = zipcodes.zipcode )
WHERE species_id = 1
AND pet_state='available'
AND center_box @ zipcodes.earth_coords
ORDER BY RADIUS;