On 2/14/07, Tom Lane <tgl@xxxxxxxxxxxxx> 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
