pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: explain analyze output for review (was: optimizing a geo_distance()...)
5+ messages / 4 participants
[nested] [flat]
* Re: explain analyze output for review (was: optimizing a geo_distance()...)
@ 2007-02-06 14:39 Mark Stosberg <mark@summersault.com>
2007-02-06 14:53 ` Re: explain analyze output for review (was: optimizing a geo_distance()...) Adam Rich <adam.r@sbcglobal.net>
2007-02-06 15:45 ` Re: explain analyze output for review (was: optimizing a geo_distance()...) Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 5+ messages in thread
From: Mark Stosberg @ 2007-02-06 14:39 UTC (permalink / raw)
To: 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.739 rows=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.779 rows=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=1 loops=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::double precision, 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=1223 loops=1727)
Index Cond: (cube_enlarge(('(-2512840.11676572, 4646218.19036629, 3574817.21369166)'::cube)::cube, 160930.130863421::double precision, 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.015 rows=1 loops=1727)
Index Cond: (("outer".postal_code_for_joining)::text = (earth_distance.zipcode)::text)
Total runtime: 1913.099 ms
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: explain analyze output for review (was: optimizing a geo_distance()...)
2007-02-06 14:39 Re: explain analyze output for review (was: optimizing a geo_distance()...) Mark Stosberg <mark@summersault.com>
@ 2007-02-06 14:53 ` Adam Rich <adam.r@sbcglobal.net>
1 sibling, 0 replies; 5+ messages in thread
From: Adam Rich @ 2007-02-06 14:53 UTC (permalink / raw)
To: 'Mark Stosberg' <mark@summersault.com>; pgsql-performance
If I'm reading this correctly, 89% of the query time is spent
doing an index scan of earth_coords_idx. Scanning pets is only
taking 6% of the total time.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mark
Stosberg
Sent: Tuesday, February 06, 2007 8:40 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] explain analyze output for review (was:
optimizing a geo_distance()...)
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.739
rows=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.779
rows=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=1
loops=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::double precision, 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=1223
loops=1727)
Index Cond: (cube_enlarge(('(-2512840.11676572,
4646218.19036629, 3574817.21369166)'::cube)::cube,
160930.130863421::double precision, 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.015 rows=1
loops=1727)
Index Cond:
(("outer".postal_code_for_joining)::text =
(earth_distance.zipcode)::text)
Total runtime: 1913.099 ms
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: explain analyze output for review (was: optimizing a geo_distance()...)
2007-02-06 14:39 Re: explain analyze output for review (was: optimizing a geo_distance()...) Mark Stosberg <mark@summersault.com>
@ 2007-02-06 15:45 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2007-02-06 15:45 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
On Tue, Feb 06, 2007 at 09:39:54 -0500,
Mark Stosberg <mark@summersault.com> wrote:
>
> 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
It probably won't pay to make partial indexes on sex or species (at least
for the popular ones), as you aren't likely to save enough by eliminating only
half the cases to make up for maintaining another index. A partial index for
active rows probably does make sense.
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: explain analyze output for review (was: optimizing a geo_distance()...)
@ 2007-02-06 14:50 Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
0 siblings, 0 replies; 5+ messages in thread
From: Hiltibidal, Robert @ 2007-02-06 14:50 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; pgsql-performance
Aqua data studio has a graphical explain built into it. It supports most
rdbms including postgres. Its what I use to performance tune DB2.
http://www.aquafold.com/
Index ANDing would suit you here
You have 3 tables with 3 possible indexes and it sounds like the query
is doing table scans where it needs to use indexes.
If your version of postgres does not support index anding another way
around this is to create a view and then index the view (if indexing
views are possible in postgres)
Another possible solution is inserting your data into a single table and
then indexing that table. The initial cost is consuming however if you
use triggers on your parent tables to automatically insert data into the
new table it becomes almost hands free.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mark
Stosberg
Sent: Tuesday, February 06, 2007 8:40 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] explain analyze output for review (was:
optimizing a geo_distance()...)
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.739
rows=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.779
rows=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=1
loops=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::double precision, 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=1223
loops=1727)
Index Cond: (cube_enlarge(('(-2512840.11676572,
4646218.19036629, 3574817.21369166)'::cube)::cube,
160930.130863421::double precision, 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.015 rows=1
loops=1727)
Index Cond:
(("outer".postal_code_for_joining)::text =
(earth_distance.zipcode)::text)
Total runtime: 1913.099 ms
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: explain analyze output for review (was: optimizing a geo_distance()...)
@ 2007-02-06 15:04 Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
0 siblings, 0 replies; 5+ messages in thread
From: Hiltibidal, Robert @ 2007-02-06 15:04 UTC (permalink / raw)
To: Adam Rich <adam.r@sbcglobal.net>; Mark Stosberg <mark@summersault.com>; pgsql-performance
what is the size of that index?
Have you considered breaking the index into components, ie more than one
index on the table?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Adam Rich
Sent: Tuesday, February 06, 2007 8:54 AM
To: 'Mark Stosberg'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] explain analyze output for review (was:
optimizing a geo_distance()...)
If I'm reading this correctly, 89% of the query time is spent
doing an index scan of earth_coords_idx. Scanning pets is only
taking 6% of the total time.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mark
Stosberg
Sent: Tuesday, February 06, 2007 8:40 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] explain analyze output for review (was:
optimizing a geo_distance()...)
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.739
rows=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.779
rows=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=1
loops=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::double precision, 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=1223
loops=1727)
Index Cond: (cube_enlarge(('(-2512840.11676572,
4646218.19036629, 3574817.21369166)'::cube)::cube,
160930.130863421::double precision, 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.015 rows=1
loops=1727)
Index Cond:
(("outer".postal_code_for_joining)::text =
(earth_distance.zipcode)::text)
Total runtime: 1913.099 ms
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-02-06 15:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-06 14:39 Re: explain analyze output for review (was: optimizing a geo_distance()...) Mark Stosberg <mark@summersault.com>
2007-02-06 14:53 ` Adam Rich <adam.r@sbcglobal.net>
2007-02-06 15:45 ` Bruno Wolff III <bruno@wolff.to>
2007-02-06 14:50 Re: explain analyze output for review (was: optimizing a geo_distance()...) Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
2007-02-06 15:04 Re: explain analyze output for review (was: optimizing a geo_distance()...) Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox