Re: explain analyze output for review (was: optimizing a geo_distance()...) - Mailing list pgsql-performance

Bruno Wolff III wrote:
>
> Some people here may be able to tell you more if you show us explain
> analyze output.

Here is my explain analyze output. Some brief context of what's going
on. The goal is to find "Pets Near You".

We join the pets table on the shelters table to get a zipcode, and then
join a shelters table with "earth_distance" to get the coordinates of
the zipcode.  (  Is there any significant penalty for using a varchar vs
an int for a joint? ).

I've been investigating partial indexes for the pets table. It has about
300,000 rows, but only about 10 are "active", and those are the ones we
are care about. Queries are also frequently made on males vs females, dogs vs cats
or specific ages, and those specific cases seem like possible candidates for partial indexes
as well. I played with that approach some, but had trouble coming up with any thing that
benchmarked faster.

I'm reading the explain analyze output correctly myself, nearly all of
the time spent is related to the 'pets' table, but I can't see what to
about it.

Help appreciated!

  Mark

Nested Loop  (cost=11.82..29.90 rows=1 width=0) (actual time=37.601..1910.787 rows=628 loops=1)
   ->  Nested Loop  (cost=6.68..20.73 rows=1 width=24) (actual time=35.525..166.547 rows=1727 loops=1)
         ->  Bitmap Heap Scan on pets  (cost=6.68..14.71 rows=1 width=4) (actual time=35.427..125.594 rows=1727
loops=1)
               Recheck Cond: (((sex)::text = 'f'::text) AND (species_id = 1))
               Filter: ((pet_state)::text = 'available'::text)
               ->  BitmapAnd  (cost=6.68..6.68 rows=2 width=0) (actual time=33.398..33.398 rows=0 loops=1)
                     ->  Bitmap Index Scan on pets_sex_idx  (cost=0.00..3.21 rows=347 width=0) (actual
time=14.739..14.739rows=35579 loops=1) 
                           Index Cond: ((sex)::text = 'f'::text)
                     ->  Bitmap Index Scan on pet_species_id_idx  (cost=0.00..3.21 rows=347 width=0) (actual
time=16.779..16.779rows=48695 loops=1) 
                           Index Cond: (species_id = 1)
         ->  Index Scan using shelters_pkey on shelters  (cost=0.00..6.01 rows=1 width=28) (actual time=0.012..0.014
rows=1loops=1727) 
               Index Cond: ("outer".shelter_id = shelters.shelter_id)
   ->  Bitmap Heap Scan on earth_distance  (cost=5.14..9.15 rows=1 width=9) (actual time=0.984..0.984 rows=0
loops=1727)
         Recheck Cond: ((cube_enlarge(('(-2512840.11676572, 4646218.19036629, 3574817.21369166)'::cube)::cube,
160930.130863421::doubleprecision, 3) @ earth_distance.earth_coords) AND 
(("outer".postal_code_for_joining)::text = (earth_distance.zipcode)::text))
         ->  BitmapAnd  (cost=5.14..5.14 rows=1 width=0) (actual time=0.978..0.978 rows=0 loops=1727)
               ->  Bitmap Index Scan on earth_coords_idx  (cost=0.00..2.15 rows=42 width=0) (actual time=0.951..0.951
rows=1223loops=1727) 
                     Index Cond: (cube_enlarge(('(-2512840.11676572, 4646218.19036629, 3574817.21369166)'::cube)::cube,
160930.130863421::doubleprecision, 3) @ earth_coords) 
               ->  Bitmap Index Scan on earth_distance_zipcode_idx  (cost=0.00..2.74 rows=212 width=0) (actual
time=0.015..0.015rows=1 loops=1727) 
                     Index Cond: (("outer".postal_code_for_joining)::text = (earth_distance.zipcode)::text)
 Total runtime: 1913.099 ms