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

On 2/13/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 2/12/07, Mark Stosberg <mark@summersault.com> wrote:
> > Merlin Moncure wrote:
> > >
> > >> Here the basic query I'm using:
> > >> SELECT
> > >>  -- 1609.344 is a constant for "meters per mile"
> > >>  cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
> > >> '90210') , earth_coords)/1609.344
> > >>    AS RADIUS
> > >>    FROM pets
> > >>    -- "shelters_active" is a view where "shelter_state = 'active'"
> > >>    JOIN shelters_active as shelters USING (shelter_id)
> > >>    -- The zipcode fields here are varchars
> > >>    JOIN zipcodes ON (
> > >>         shelters.postal_code_for_joining = zipcodes.zipcode )
> > >>    -- search for just 'dogs'
> > >>    WHERE species_id = 1
> > >>        AND pet_state='available'
> > >>       AND earth_box(
> > >>         (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
> > >> 10*1609.344
> > >>       ) @ earth_coords
> > >>    ORDER BY RADIUS;
> > >
> > It may not have been clear from the query, but only the 'zipcodes' table
> > has an 'earth_coords' column. Also, I think your refactoring means
> > something different. My query expresses "number of miles this pet is
> > from 90210", while I think the refactor expresses a distance between a
> > pet and another calculated value.
>
> 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.

try this:

SELECT * FROM
(
SELECT
  earth_coords(q.earth_coords, s.earth_coords)/1609.344 as radius
  FROM pets
  JOIN shelters_active as shelters USING (shelter_id)
  JOIN zipcodes s ON shelters.postal_code_for_joining = zipcodes.zipcode
  JOIN zipcodes q ON q.zipcode = '90210'
  WHERE species_id = 1
    AND pet_state='available'
    AND earth_box(q.earth_coords, 10*1609.344) @ s.earth_coords
) p order by radius

merlin