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

On 2/14/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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