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

On 2/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> There are two things wrong here: first, that the estimated row count is
> only 20% of actual; it should certainly not be that far off for such a
> simple condition.  I wonder if your vacuum/analyze procedures are
> actually working.  Second, you mentioned somewhere along the line that
> 'available' pets are about 10% of all the entries, which means that this
> indexscan is more than likely entirely counterproductive: it would be
> cheaper to ignore this index altogether.

I think switching the index on pet_state to a composite on (pet_state,
species_id) might help too.

or even better:

create function is_pet_available(text) returns bool as
$$
  select $1='available';
$$ language sql immutable;

create index pets_available_species_idx on
pets(is_pet_available(pet_state), species_id);

refactor your query something similar to:

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
   is_pet_available(pet_state)
   AND species_id = 1
   AND earth_box(q.earth_coords, 10*1609.344) @ s.earth_coords
) p order by radius

merlin