pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcube operations slower than geo_distance() on production server
11+ messages / 3 participants
[nested] [flat]
* cube operations slower than geo_distance() on production server
@ 2007-02-09 19:26 Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mark Stosberg @ 2007-02-09 19:26 UTC (permalink / raw)
To: pgsql-performance
With the help of some of this list, I was able to successfully set up
and benchmark a cube-based replacement for geo_distance() calculations.
On a development box, the cube-based variations benchmarked consistently
running in about 1/3 of the time of the gel_distance() equivalents.
After setting up the same columns and indexes on a production
database, it's a different story. All the cube operations show
themselves to be about the same as, or noticeably slower than, the same
operations done with geo_distance().
I've stared at the EXPLAIN ANALYZE output as much I can to figure what's
gone. Could you help?
Here's the plan on the production server, which seems too slow. Below is the plan I get in
on the development server, which is much faster.
I tried "set enable_nestloop = off", which did change the plan, but the performance.
The production DB has much more data in it, but I still expected comparable results relative
to using geo_distance() calculations.
The production db gets a "VACUUM ANALYZE" every couple of hours now.
Thanks!
Mark
########
Sort (cost=6617.03..6617.10 rows=27 width=32) (actual time=2482.915..2487.008 rows=1375 loops=1)
Sort Key: (cube_distance($0, zipcodes.earth_coords) / 1609.344::double precision)
InitPlan
-> Index Scan using zipcodes_pkey on zipcodes (cost=0.00..3.01 rows=1 width=32) (actual time=0.034..0.038 rows=1 loops=1)
Index Cond: ((zipcode)::text = '90210'::text)
-> Index Scan using zipcodes_pkey on zipcodes (cost=0.00..3.01 rows=1 width=32) (actual time=0.435..0.438 rows=1 loops=1)
Index Cond: ((zipcode)::text = '90210'::text)
-> Nested Loop (cost=538.82..6610.36 rows=27 width=32) (actual time=44.660..2476.919 rows=1375 loops=1)
-> Nested Loop (cost=2.15..572.14 rows=9 width=36) (actual time=4.877..39.037 rows=136 loops=1)
-> Bitmap Heap Scan on zipcodes (cost=2.15..150.05 rows=42 width=41) (actual time=3.749..4.951 rows=240 loops=1)
Recheck Cond: (cube_enlarge(($1)::cube, 16093.4357308298::double precision, 3) @ earth_coords)
-> Bitmap Index Scan on zip_earth_coords_idx (cost=0.00..2.15 rows=42 width=0) (actual time=3.658..3.658 rows=240 loops=1)
Index Cond: (cube_enlarge(($1)::cube, 16093.4357308298::double precision, 3) @ earth_coords)
-> Index Scan using shelters_postal_code_for_joining_idx on shelters (cost=0.00..10.02 rows=2 width=12) (actual time=0.079..0.133 rows=1 loops=240)
Index Cond: ((shelters.postal_code_for_joining)::text = ("outer".zipcode)::text)
-> Bitmap Heap Scan on pets (cost=536.67..670.47 rows=34 width=4) (actual time=16.844..17.830 rows=10 loops=136)
Recheck Cond: ((pets.shelter_id = "outer".shelter_id) AND ((pets.pet_state)::text = 'available'::text))
Filter: (species_id = 1) Sort (cost=7004.53..7004.62 rows=39 width=32) (actual time=54.635..55.450 rows=475 loops=1)
-> BitmapAnd (cost=536.67..536.67 rows=34 width=0) (actual time=16.621..16.621 rows=0 loops=136)
-> Bitmap Index Scan on pets_shelter_id_idx (cost=0.00..3.92 rows=263 width=0) (actual time=0.184..0.184 rows=132 loops=136)
Index Cond: (pets.shelter_id = "outer".shelter_id)
-> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..532.50 rows=39571 width=0) (actual time=26.922..26.922 rows=40390 loops=82)
Index Cond: ((pet_state)::text = 'available'::text)
Total runtime: 2492.852 ms
########### Faster plan in development:
Sort (cost=7004.53..7004.62 rows=39 width=32) (actual time=54.635..55.450 rows=475 loops=1)
Sort Key: (cube_distance($0, earth_distance.earth_coords) / 1609.344::double precision)
InitPlan
-> Bitmap Heap Scan on earth_distance (cost=4.74..624.60 rows=212 width=32) (actual time=0.113..0.115 rows=1 loops=1)
Recheck Cond: ((zipcode)::text = '90210'::text)
-> Bitmap Index Scan on earth_distance_zipcode_idx (cost=0.00..4.74 rows=212 width=0) (actual time=0.101..0.101 rows=2 loops=1)
Index Cond: ((zipcode)::text = '90210'::text)
-> Bitmap Heap Scan on earth_distance (cost=4.74..624.60 rows=212 width=32) (actual time=0.205..0.208 rows=1 loops=1)
Recheck Cond: ((zipcode)::text = '90210'::text)
-> Bitmap Index Scan on earth_distance_zipcode_idx (cost=0.00..4.74 rows=212 width=0) (actual time=0.160..0.160 rows=2 loops=1)
Index Cond: ((zipcode)::text = '90210'::text)
-> Hash Join (cost=618.67..5754.30 rows=39 width=32) (actual time=13.499..52.924 rows=475 loops=1)
Hash Cond: ("outer".shelter_id = "inner".shelter_id)
-> Bitmap Heap Scan on pets (cost=44.85..5158.42 rows=4298 width=4) (actual time=4.278..34.192 rows=3843 loops=1)
Recheck Cond: ((pet_state)::text = 'available'::text)
Filter: (species_id = 1)
-> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..44.85 rows=6244 width=0) (actual time=3.623..3.623 rows=7257 loops=1)
Index Cond: ((pet_state)::text = 'available'::text)
-> Hash (cost=573.65..573.65 rows=66 width=36) (actual time=8.916..8.916 rows=102 loops=1)
-> Nested Loop (cost=3.15..573.65 rows=66 width=36) (actual time=3.004..8.513 rows=102 loops=1)
-> Bitmap Heap Scan on earth_distance (cost=3.15..152.36 rows=42 width=41) (actual time=2.751..3.432 rows=240 loops=1)
Recheck Cond: (cube_enlarge(($1)::cube, 16093.4357308298::double precision, 3) @ earth_coords)
-> Bitmap Index Scan on earth_coords_idx (cost=0.00..3.15 rows=42 width=0) (actual time=2.520..2.520 rows=480 loops=1)
Index Cond: (cube_enlarge(($1)::cube, 16093.4357308298::double precision, 3) @ earth_coords)
-> Index Scan using shelters_postal_code_for_joining_idx on shelters (cost=0.00..10.01 rows=2 width=12) (actual time=0.011..0.015 rows=0 loops=240)
Index Cond: ((shelters.postal_code_for_joining)::text = ("outer".zipcode)::text)
Total runtime: 58.038 ms
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-10 01:31 Merlin Moncure <mmoncure@gmail.com>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 11+ messages in thread
From: Merlin Moncure @ 2007-02-10 01:31 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
On 2/10/07, Mark Stosberg <mark@summersault.com> wrote:
>
> With the help of some of this list, I was able to successfully set up
> and benchmark a cube-based replacement for geo_distance() calculations.
>
> On a development box, the cube-based variations benchmarked consistently
> running in about 1/3 of the time of the gel_distance() equivalents.
>
> After setting up the same columns and indexes on a production
> database, it's a different story. All the cube operations show
> themselves to be about the same as, or noticeably slower than, the same
> operations done with geo_distance().
>
> I've stared at the EXPLAIN ANALYZE output as much I can to figure what's
> gone. Could you help?
>
> Here's the plan on the production server, which seems too slow. Below is the plan I get in
> on the development server, which is much faster.
>
> I tried "set enable_nestloop = off", which did change the plan, but the performance.
>
> The production DB has much more data in it, but I still expected comparable results relative
> to using geo_distance() calculations.
any objection to posting the query (any maybe tables, keys, indexes, etc)?
merlin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-12 16:11 Mark Stosberg <mark@summersault.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mark Stosberg @ 2007-02-12 16:11 UTC (permalink / raw)
To: pgsql-performance
Merlin Moncure wrote:
> On 2/10/07, Mark Stosberg <mark@summersault.com> wrote:
>>
>> With the help of some of this list, I was able to successfully set up
>> and benchmark a cube-based replacement for geo_distance() calculations.
>>
>> On a development box, the cube-based variations benchmarked consistently
>> running in about 1/3 of the time of the gel_distance() equivalents.
>>
>> After setting up the same columns and indexes on a production
>> database, it's a different story. All the cube operations show
>> themselves to be about the same as, or noticeably slower than, the same
>> operations done with geo_distance().
>>
>> I've stared at the EXPLAIN ANALYZE output as much I can to figure what's
>> gone. Could you help?
>>
>> Here's the plan on the production server, which seems too slow. Below
>> is the plan I get in
>> on the development server, which is much faster.
>>
>> I tried "set enable_nestloop = off", which did change the plan, but
>> the performance.
>>
>> The production DB has much more data in it, but I still expected
>> comparable results relative
>> to using geo_distance() calculations.
>
> any objection to posting the query (any maybe tables, keys, indexes, etc)?
Here the basic query I'm using:
SELECT
-- 1609.344 is a constant for "meters per mile"
cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
'90210') , earth_coords)/1609.344
AS RADIUS
FROM pets
-- "shelters_active" is a view where "shelter_state = 'active'"
JOIN shelters_active as shelters USING (shelter_id)
-- The zipcode fields here are varchars
JOIN zipcodes ON (
shelters.postal_code_for_joining = zipcodes.zipcode )
-- search for just 'dogs'
WHERE species_id = 1
AND pet_state='available'
AND earth_box(
(SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
10*1609.344
) @ earth_coords
ORDER BY RADIUS;
All the related columns are indexed:
pets.species_id
pets.shelter_id
pets.pet_state
shelters.shelter_id (pk)
shelters.postal_code_for_joining
shelters.active
zipcodes.zipcode (pk)
zipcodes.earth_coords
The pets table has about 300,000 rows, but only about 10% are
"available". It sees regular updates and is "vacuum analyzed" every
couple of hours now. the rest of the tables get "vacuum analyzed
nightly". The shelters table is about 99% "shelter_state = active".
It's updated infrequently.
The zipcodes table has about 40,000 rows in it and doesn't change.
I tried a partial index on the pets table "WHERE pet_state =
'available'. I could see the index was used, but the performance was
unaffected.
The "EXPLAIN ANALYZE" output is attached, to try to avoid mail-client
wrapping. The query is running 10 times slower today than on Friday,
perhaps because of server load, or because we are at the end of a VACUUM
cycle.
Thanks for any help!
Mark
Attachments:
analyze.txt (text/plain, 2.3K) download
Sort (cost=6887.03..6887.10 rows=27 width=32) (actual time=17925.098..17927.979 rows=1324 loops=1)
Sort Key: (cube_distance($0, zipcodes.earth_coords) / 1609.344::double precision)
InitPlan
-> Index Scan using zipcodes_pkey on zipcodes (cost=0.00..3.01 rows=1 width=32) (actual time=0.028..0.031 rows=1 loops=1) Index Cond: ((zipcode)::text = '90210'::text)
-> Index Scan using zipcodes_pkey on zipcodes (cost=0.00..3.01 rows=1 width=32) (actual time=0.687..0.692 rows=1 loops=1) Index Cond: ((zipcode)::text = '90210'::text)
-> Nested Loop (cost=568.82..6880.36 rows=27 width=32) (actual time=346.932..17919.697 rows=1324 loops=1)
-> Nested Loop (cost=2.15..572.14 rows=9 width=36) (actual time=8.321..43.378 rows=136 loops=1)
-> Bitmap Heap Scan on zipcodes (cost=2.15..150.05 rows=42 width=41) (actual time=3.442..4.402 rows=240 loops=1)
Recheck Cond: (cube_enlarge(($1)::cube, 16093.4357308298::double precision, 3) @ earth_coords)
-> Bitmap Index Scan on zip_earth_coords_idx (cost=0.00..2.15 rows=42 width=0) (actual time=3.426..3.426 rows=240 loops=1)
Index Cond: (cube_enlarge(($1)::cube, 16093.4357308298::double precision, 3) @ earth_coord)
-> Index Scan using shelters_postal_code_for_joining_idx on shelters (cost=0.00..10.02 rows=2 width=12) (actual time=0.093..0.155 rows=1 loops=240) Index Cond: ((shelters.postal_code_for_joining)::text = ("outer".zipcode)::text)
-> Bitmap Heap Scan on pets (cost=566.67..700.47 rows=34 width=4) (actual time=130.363..131.367 rows=10 loops=136) Recheck Cond: ((pets.shelter_id = "outer".shelter_id) AND ((pets.pet_state)::text = 'available'::text))
Filter: (species_id = 1)
-> BitmapAnd (cost=566.67..566.67 rows=34 width=0) (actual time=129.333..129.333 rows=0 loops=136)
-> Bitmap Index Scan on pets_shelter_id_idx (cost=0.00..3.92 rows=263 width=0) (actual time=0.164..0.164 rows=178 loops=136) Index Cond: (pets.shelter_id = "outer".shelter_id)
-> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..562.50 rows=39571 width=0) (actual time=213.620..213.620 rows=195599 loops=82)
Index Cond: ((pet_state)::text = 'available'::text)
Total runtime: 17933.675 ms
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-12 19:03 Merlin Moncure <mmoncure@gmail.com>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 2 replies; 11+ messages in thread
From: Merlin Moncure @ 2007-02-12 19:03 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
On 2/12/07, Mark Stosberg <mark@summersault.com> wrote:
> Merlin Moncure wrote:
> > On 2/10/07, Mark Stosberg <mark@summersault.com> wrote:
> >>
> >> With the help of some of this list, I was able to successfully set up
> >> and benchmark a cube-based replacement for geo_distance() calculations.
> >>
> >> On a development box, the cube-based variations benchmarked consistently
> >> running in about 1/3 of the time of the gel_distance() equivalents.
> >>
> >> After setting up the same columns and indexes on a production
> >> database, it's a different story. All the cube operations show
> >> themselves to be about the same as, or noticeably slower than, the same
> >> operations done with geo_distance().
> >>
> >> I've stared at the EXPLAIN ANALYZE output as much I can to figure what's
> >> gone. Could you help?
> >>
> >> Here's the plan on the production server, which seems too slow. Below
> >> is the plan I get in
> >> on the development server, which is much faster.
> >>
> >> I tried "set enable_nestloop = off", which did change the plan, but
> >> the performance.
> >>
> >> The production DB has much more data in it, but I still expected
> >> comparable results relative
> >> to using geo_distance() calculations.
> >
> > any objection to posting the query (any maybe tables, keys, indexes, etc)?
>
> Here the basic query I'm using:
> SELECT
> -- 1609.344 is a constant for "meters per mile"
> cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
> '90210') , earth_coords)/1609.344
> AS RADIUS
> FROM pets
> -- "shelters_active" is a view where "shelter_state = 'active'"
> JOIN shelters_active as shelters USING (shelter_id)
> -- The zipcode fields here are varchars
> JOIN zipcodes ON (
> shelters.postal_code_for_joining = zipcodes.zipcode )
> -- search for just 'dogs'
> WHERE species_id = 1
> AND pet_state='available'
> AND earth_box(
> (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
> 10*1609.344
> ) @ earth_coords
> ORDER BY RADIUS;
your query looks a bit funky. here are the problems I see.
* in your field list, you don't need to re-query the zipcode table.
> cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
> '90210') , earth_coords)/1609.344 AS RADIUS
becomes
cube_distance(pets.earth_coords, earth_coords ) / 1609.344 AS RADIUS
also, dont. re-refer to the zipcodes table in the join clause. you are
already joining to it:
> AND earth_box(
> (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
> 10*1609.344) @ earth_coords
becomes
AND earth_box(zipcodes.earth_coords, 10*1609.344) ) @ pets.earth_coords
* also, does pet_state have any other states than 'available' and '
not available'? if not, you should be using a boolean. if so, you can
consider a functional index to convert it to a booelan.
* if you always look up pets by species, we can explore composite
index columns on species, available (especially using the above
functional suggestion), etc. composite > partial (imo)
thats just to start. play with it and see what comes up.
merlin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-12 19:05 Merlin Moncure <mmoncure@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Merlin Moncure @ 2007-02-12 19:05 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
On 2/12/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> cube_distance(pets.earth_coords, earth_coords ) / 1609.344 AS RADIUS
this should read:
cube_distance(pets.earth_coords, zipcodes.earth_coords ) / 1609.344 AS RADIUS
merlin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-12 19:48 Mark Stosberg <mark@summersault.com>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Mark Stosberg @ 2007-02-12 19:48 UTC (permalink / raw)
To: pgsql-performance
Merlin--
Thanks so much for your help. Some follow-ups are below.
Merlin Moncure wrote:
>
>> Here the basic query I'm using:
>> SELECT
>> -- 1609.344 is a constant for "meters per mile"
>> cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
>> '90210') , earth_coords)/1609.344
>> AS RADIUS
>> FROM pets
>> -- "shelters_active" is a view where "shelter_state = 'active'"
>> JOIN shelters_active as shelters USING (shelter_id)
>> -- The zipcode fields here are varchars
>> JOIN zipcodes ON (
>> shelters.postal_code_for_joining = zipcodes.zipcode )
>> -- search for just 'dogs'
>> WHERE species_id = 1
>> AND pet_state='available'
>> AND earth_box(
>> (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
>> 10*1609.344
>> ) @ earth_coords
>> ORDER BY RADIUS;
>
> your query looks a bit funky. here are the problems I see.
>
> * in your field list, you don't need to re-query the zipcode table.
>> cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
>> '90210') , earth_coords)/1609.344 AS RADIUS
>
> becomes
>
> cube_distance(pets.earth_coords, earth_coords ) / 1609.344 AS RADIUS
It may not have been clear from the query, but only the 'zipcodes' table
has an 'earth_coords' column. Also, I think your refactoring means
something different. My query expresses "number of miles this pet is
from 90210", while I think the refactor expresses a distance between a
pet and another calculated value.
> also, dont. re-refer to the zipcodes table in the join clause. you are
> already joining to it:
>> AND earth_box(
>> (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
>> 10*1609.344) @ earth_coords
>
> becomes
>
> AND earth_box(zipcodes.earth_coords, 10*1609.344) ) @ pets.earth_coords
I have the same question here as above-- I don't see how the new syntax
includes the logic of "distance from the 90210 zipcode".
> * also, does pet_state have any other states than 'available' and '
> not available'? if not, you should be using a boolean. if so, you can
> consider a functional index to convert it to a booelan.
Yes, it has three states.
> * if you always look up pets by species, we can explore composite
> index columns on species, available (especially using the above
> functional suggestion), etc. composite > partial (imo)
We nearly always search by species. Right now it's mostly dogs and some
cats. I searched for references to composite index columns, and didn't
find much. Could you provide a direct reference to what you have in
mind?
Any other ideas appreciated!
Mark
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-13 14:15 Merlin Moncure <mmoncure@gmail.com>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 11+ messages in thread
From: Merlin Moncure @ 2007-02-13 14:15 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
On 2/12/07, Mark Stosberg <mark@summersault.com> wrote:
> Merlin Moncure wrote:
> >
> >> Here the basic query I'm using:
> >> SELECT
> >> -- 1609.344 is a constant for "meters per mile"
> >> cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
> >> '90210') , earth_coords)/1609.344
> >> AS RADIUS
> >> FROM pets
> >> -- "shelters_active" is a view where "shelter_state = 'active'"
> >> JOIN shelters_active as shelters USING (shelter_id)
> >> -- The zipcode fields here are varchars
> >> JOIN zipcodes ON (
> >> shelters.postal_code_for_joining = zipcodes.zipcode )
> >> -- search for just 'dogs'
> >> WHERE species_id = 1
> >> AND pet_state='available'
> >> AND earth_box(
> >> (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
> >> 10*1609.344
> >> ) @ earth_coords
> >> ORDER BY RADIUS;
> >
> It may not have been clear from the query, but only the 'zipcodes' table
> has an 'earth_coords' column. Also, I think your refactoring means
> something different. My query expresses "number of miles this pet is
> from 90210", while I think the refactor expresses a distance between a
> pet and another calculated value.
my mistake, i misunderstood what you were trying to do...can you try
removing the 'order by radius' and see if it helps? if not, we can try
working on this query some more. There is a better, faster way to do
this, I'm sure of it.
merlin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-13 14:31 Merlin Moncure <mmoncure@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Merlin Moncure @ 2007-02-13 14:31 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
On 2/13/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 2/12/07, Mark Stosberg <mark@summersault.com> wrote:
> > Merlin Moncure wrote:
> > >
> > >> Here the basic query I'm using:
> > >> SELECT
> > >> -- 1609.344 is a constant for "meters per mile"
> > >> cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
> > >> '90210') , earth_coords)/1609.344
> > >> AS RADIUS
> > >> FROM pets
> > >> -- "shelters_active" is a view where "shelter_state = 'active'"
> > >> JOIN shelters_active as shelters USING (shelter_id)
> > >> -- The zipcode fields here are varchars
> > >> JOIN zipcodes ON (
> > >> shelters.postal_code_for_joining = zipcodes.zipcode )
> > >> -- search for just 'dogs'
> > >> WHERE species_id = 1
> > >> AND pet_state='available'
> > >> AND earth_box(
> > >> (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
> > >> 10*1609.344
> > >> ) @ earth_coords
> > >> ORDER BY RADIUS;
> > >
> > It may not have been clear from the query, but only the 'zipcodes' table
> > has an 'earth_coords' column. Also, I think your refactoring means
> > something different. My query expresses "number of miles this pet is
> > from 90210", while I think the refactor expresses a distance between a
> > pet and another calculated value.
>
> my mistake, i misunderstood what you were trying to do...can you try
> removing the 'order by radius' and see if it helps? if not, we can try
> working on this query some more. There is a better, faster way to do
> this, I'm sure of it.
try this:
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 species_id = 1
AND pet_state='available'
AND earth_box(q.earth_coords, 10*1609.344) @ s.earth_coords
) p order by radius
merlin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-13 20:47 Mark Stosberg <mark@summersault.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mark Stosberg @ 2007-02-13 20:47 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
On Tue, Feb 13, 2007 at 09:31:18AM -0500, Merlin Moncure wrote:
>
> >my mistake, i misunderstood what you were trying to do...can you try
> >removing the 'order by radius' and see if it helps? if not, we can try
> >working on this query some more. There is a better, faster way to do
> >this, I'm sure of it.
Merlin,
Thanks again for your help. I did try without the "order by", and it
didn't make more difference.
> try this:
Based on your example, I was able to further refine the query to remove
the duplicate sub-selects that I had. However, this didn't seem to
improve performance.
I'm still stuck with the same essential problem: On the development
server, where is less data (400 results returns vs 1300), the cube
search is at least twice as fast, but on the production server, it is
consistently slower.
So, either the difference is one of scale, or I have some different
configuration detail in production that is causing the issue.
For reference, here's two versions of the query. The first uses
the old geo_distance(), and the second one is the new cube query I'm
trying, inspired by your suggested refactoring.
It's not surprising to me that the queries run at different speeds
on different servers, but it /is/ surprising that their relative speeds
reverse!
Mark
-- Searching for all dogs within 10 miles of 90210 zipcode
EXPLAIN ANALYZE
SELECT
zipcodes.lon_lat <@> center.lon_lat AS radius
FROM (SELECT lon_lat FROM zipcodes WHERE zipcode = '90210') as center,
pets
JOIN shelters_active as shelters USING (shelter_id)
JOIN zipcodes on (shelters.postal_code_for_joining = zipcodes.zipcode)
WHERE species_id = 1
AND pet_state='available'
AND (zipcodes.lon_lat <@> center.lon_lat) < 10
ORDER BY RADIUS;
EXPLAIN ANALYZE
SELECT
cube_distance( center.earth_coords , zipcodes.earth_coords)/1609.344
AS RADIUS
FROM (SELECT
earth_coords,
earth_box( earth_coords , 10*1609.344 ) as center_box
from zipcodes WHERE zipcode = '90210'
) AS center,
pets
JOIN shelters_active AS shelters USING (shelter_id)
JOIN zipcodes ON ( shelters.postal_code_for_joining = zipcodes.zipcode )
WHERE species_id = 1
AND pet_state='available'
AND center_box @ zipcodes.earth_coords
ORDER BY RADIUS;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-14 07:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2007-02-14 07:15 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Mark Stosberg <mark@summersault.com> writes:
> For reference, here's two versions of the query. The first uses
> the old geo_distance(), and the second one is the new cube query I'm
> trying, inspired by your suggested refactoring.
You didn't show EXPLAIN ANALYZE output :-(
Looking back in the thread, the last E.A. output I see is in your
message of 2/12 11:11, and the striking thing there is that it seems all
the time is going into one indexscan:
-> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..562.50 rows=39571 width=0) (actual time=213.620..213.620 rows=195599 loops=82)
Index Cond: ((pet_state)::text = 'available'::text)
Total runtime: 17933.675 ms
213.620 * 82 = 17516.840, so this step is all but 400msec of the run.
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.
Suggestions:
1. ANALYZE the table by hand, try the explain again and see if this
rowcount estimate gets better. If so, you need to look into why your
existing procedures aren't keeping the stats up to date.
2. If, with a more accurate rowcount estimate, the planner still wants
to use this index, try discouraging it. Brute force would be to drop
the index. If there are less-common pet_states that are actually worth
searching for, maybe keep the index but change it to a partial index
WHERE pet_state != 'available'.
Also, I don't see that you mentioned anywhere what PG version you are
running, but if it's not the latest then an update might help. I recall
having fixed a bug that made the planner too eager to AND on an index
that wouldn't actually help much ... which seems to fit this problem
description pretty well.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: cube operations slower than geo_distance() on production server
@ 2007-02-14 13:58 Merlin Moncure <mmoncure@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Merlin Moncure @ 2007-02-14 13:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Stosberg <mark@summersault.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-02-14 13:58 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-09 19:26 cube operations slower than geo_distance() on production server Mark Stosberg <mark@summersault.com>
2007-02-10 01:31 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-12 16:11 ` Mark Stosberg <mark@summersault.com>
2007-02-12 19:03 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-12 19:05 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-12 19:48 ` Mark Stosberg <mark@summersault.com>
2007-02-13 14:15 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-13 14:31 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-13 20:47 ` Mark Stosberg <mark@summersault.com>
2007-02-14 07:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-14 13:58 ` Merlin Moncure <mmoncure@gmail.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