pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance weirdness with/without vacuum analyze
7+ messages / 4 participants
[nested] [flat]
* Performance weirdness with/without vacuum analyze
@ 2003-10-20 16:50 Harry Broomhall <harry.broomhall@uk.easynet.net>
0 siblings, 1 reply; 7+ messages in thread
From: Harry Broomhall @ 2003-10-20 16:50 UTC (permalink / raw)
To: pgsql-performance
It has been suggested to me that I resubmit this question to this list,
rather than the GENERAL list it was originaly sent to.
I asked earlier about ways of doing an UPDATE involving a left outer
join and got some very useful feedback.
This has thrown up a (to me) strange anomaly about the speed of such
an update.
The input to this query is a fairly large (the example I'm working
with has 335,000 rows) set of records containing numbers to be looked
up in the lookup table. This lookup table has 239 rows.
I'm always reading the suggestion that doing a 'VACUUM ANALYZE' on a
database is 'A Good Thing' as it helps the planner to do the best thing, so
I arranged a vacuum analyze on the input records.
Running the query takes about 13 mins or so.
If, however I *don't* do an analyze, but leave the input table as
it was when imported the run takes about 2.5 mins!
Looking at the output from 'explain' I can see that the main difference
in the way the planner does it is that it does a merge join in the non-analyze
case, and a hash join in the analyze case.
Unfortunately I don't really know what this is implying, hence the call
for assistance.
I have a file with all sorts of info about the problem (details of tables,
output of 'explain' etc) but as it is about 5K in size, and wide as well, I
didn't want to dump it in the list without any warning!
However - it has been suggested that it should be OK to include this I have
now done so - hopefully with this message.
Regards,
Harry.
Attachments:
run.info.txt (text/plain, 5.0K) download
select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.4 on i386-unknown-freebsd4.5, compiled by GCC 2.95.3
create table num_xlate (interim_num varchar(30) not null , num varchar(30) not null, starttime timestamp with time zone not null, endtime timestamp with time zone not null, constraint num_pos_dur check (endtime >= starttime), primary key (interim_num, starttime));
create table unrated_cdrs (cdr_id bigserial unique, interim_cli varchar(30), interim_tli varchar(30), cli varchar(30), tli varchar(30));
CREATE TABLE
copy unrated_cdrs (interim_cli, interim_tli) from '/data/swipe/bin/mt2.csv' with delimiter as ',';
COPY
explain update unrated_cdrs set cli = coalesce(b.num, unrated_cdrs.interim_cli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_cli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Merge Join (cost=286.99..358.99 rows=1000 width=393)
Merge Cond: ("outer".cdr_id = "inner".cdr_id)
-> Index Scan using unrated_cdrs_cdr_id_key on unrated_cdrs (cost=0.00..52.00 rows=1000 width=262)
-> Sort (cost=286.99..289.49 rows=1000 width=131)
Sort Key: un.cdr_id
-> Merge Join (cost=139.66..237.16 rows=1000 width=131)
Merge Cond: ("outer".interim_cli = "inner".interim_num)
Join Filter: (("outer".starttime >= "inner".starttime) AND ("outer".starttime <= "inner".endtime))
-> Sort (cost=69.83..72.33 rows=1000 width=49)
Sort Key: un.interim_cli
-> Seq Scan on unrated_cdrs un (cost=0.00..20.00 rows=1000 width=49)
-> Sort (cost=69.83..72.33 rows=1000 width=82)
Sort Key: num_xlate.interim_num
-> Seq Scan on num_xlate (cost=0.00..20.00 rows=1000 width=82)
(14 rows)
update unrated_cdrs set cli = coalesce(b.num, unrated_cdrs.interim_cli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_cli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id;
UPDATE 335671
update unrated_cdrs set tli = coalesce(b.num, unrated_cdrs.interim_tli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_tli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id;
UPDATE 335671
2m57.37s real 0.00s user 0.00s sys
DROP TABLE
create table unrated_cdrs (cdr_id bigserial unique, interim_cli varchar(30), interim_tli varchar(30), cli varchar(30), tli varchar(30));
CREATE TABLE
copy unrated_cdrs (interim_cli, interim_tli) from '/data/swipe/bin/mt2.csv' with delimiter as ',';
COPY
vacuum analyze unrated_cdrs;
VACUUM
explain update unrated_cdrs set cli = coalesce(b.num, unrated_cdrs.interim_cli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_cli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Hash Join (cost=67773.77..112554.43 rows=335671 width=343)
Hash Cond: ("outer".cdr_id = "inner".cdr_id)
-> Seq Scan on unrated_cdrs (cost=0.00..8832.71 rows=335671 width=229)
-> Hash (cost=61197.59..61197.59 rows=335671 width=114)
-> Merge Join (cost=58661.58..61197.59 rows=335671 width=114)
Merge Cond: ("outer".interim_cli = "inner".interim_num)
Join Filter: (("outer".starttime >= "inner".starttime) AND ("outer".starttime <= "inner".endtime))
-> Sort (cost=58591.75..59430.93 rows=335671 width=32)
Sort Key: un.interim_cli
-> Seq Scan on unrated_cdrs un (cost=0.00..8832.71 rows=335671 width=32)
-> Sort (cost=69.83..72.33 rows=1000 width=82)
Sort Key: num_xlate.interim_num
-> Seq Scan on num_xlate (cost=0.00..20.00 rows=1000 width=82)
(13 rows)
update unrated_cdrs set cli = coalesce(b.num, unrated_cdrs.interim_cli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_cli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id;
UPDATE 335671
update unrated_cdrs set tli = coalesce(b.num, unrated_cdrs.interim_tli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_tli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id;
UPDATE 335671
13m43.74s real 0.00s user 0.00s sys
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance weirdness with/without vacuum analyze
@ 2003-10-20 17:28 Josh Berkus <josh@agliodbs.com>
parent: Harry Broomhall <harry.broomhall@uk.easynet.net>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2003-10-20 17:28 UTC (permalink / raw)
To: Harry Broomhall <harry.broomhall@uk.easynet.net>; pgsql-performance
Harry,
> It has been suggested to me that I resubmit this question to this list,
> rather than the GENERAL list it was originaly sent to.
>
> I asked earlier about ways of doing an UPDATE involving a left outer
> join and got some very useful feedback.
The query you posted will always be somewhat slow due to the forced join
order, which is unavodable with a left outer join.
However, regarding your peculiar behaviour, please post:
1) Your random_page_cost and effective_cache_size settings
2) The EXPLAIN ANALYZE of each query instead of just the EXPLAIN
Thanks!
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance weirdness with/without vacuum analyze
@ 2003-10-21 11:40 Harry Broomhall <harry.broomhall@uk.easynet.net>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Harry Broomhall @ 2003-10-21 11:40 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: harry.broomhall@uk.easynet.net; pgsql-performance
Josh Berkus writes:
> Harry,
Many thanks for your response,
>
> > It has been suggested to me that I resubmit this question to this list,
> > rather than the GENERAL list it was originaly sent to.
> >
> > I asked earlier about ways of doing an UPDATE involving a left outer
> > join and got some very useful feedback.
>
> The query you posted will always be somewhat slow due to the forced join
> order, which is unavodable with a left outer join.
Yes - I rather suspected that! It is a shame it takes two joins to do
the work.
>
> However, regarding your peculiar behaviour, please post:
>
> 1) Your random_page_cost and effective_cache_size settings
#effective_cache_size = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
i.e. - still set to their defaults.
> 2) The EXPLAIN ANALYZE of each query instead of just the EXPLAIN
First the case with no vacuum analyze:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=99.32..171.32 rows=1000 width=259) (actual time=18579.92..48277.69 rows=335671 loops=1)
Merge Cond: ("outer".cdr_id = "inner".cdr_id)
-> Index Scan using import_cdrs_cdr_id_key on import_cdrs (cost=0.00..52.00 rows=1000 width=164) (actual time=0.42..11479.51 rows=335671 loops=1)
-> Sort (cost=99.32..101.82 rows=1000 width=95) (actual time=18578.71..21155.65 rows=335671 loops=1)
Sort Key: un.cdr_id
-> Hash Join (cost=6.99..49.49 rows=1000 width=95) (actual time=4.70..10011.35 rows=335671 loops=1)
Hash Cond: ("outer".interim_cli = "inner".interim_num)
Join Filter: (("outer".starttime >= "inner".starttime) AND ("outer".starttime <= "inner".endtime))
-> Seq Scan on import_cdrs un (cost=0.00..20.00 rows=1000 width=49) (actual time=0.02..4265.63 rows=335671 loops=1)
-> Hash (cost=6.39..6.39 rows=239 width=46) (actual time=4.57..4.57 rows=0 loops=1)
-> Seq Scan on num_xlate (cost=0.00..6.39 rows=239 width=46) (actual time=0.12..2.77 rows=239 loops=1)
Total runtime: 80408.42 msec
(12 rows)
And now the case *with* the vacuum analyze:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=15335.91..49619.57 rows=335671 width=202) (actual time=12383.44..49297.58 rows=335671 loops=1)
Hash Cond: ("outer".cdr_id = "inner".cdr_id)
-> Seq Scan on import_cdrs (cost=0.00..8496.71 rows=335671 width=126) (actual time=0.15..9504.24 rows=335671 loops=1)
-> Hash (cost=10398.73..10398.73 rows=335671 width=76) (actual time=12371.13..12371.13 rows=0 loops=1)
-> Hash Join (cost=6.99..10398.73 rows=335671 width=76) (actual time=4.91..9412.55 rows=335671 loops=1)
Hash Cond: ("outer".interim_cli = "inner".interim_num)
Join Filter: (("outer".starttime >= "inner".starttime) AND ("outer".starttime <= "inner".endtime))
-> Seq Scan on import_cdrs un (cost=0.00..8496.71 rows=335671 width=30) (actual time=0.09..3813.54 rows=335671 loops=1)
-> Hash (cost=6.39..6.39 rows=239 width=46) (actual time=4.71..4.71 rows=0 loops=1)
-> Seq Scan on num_xlate (cost=0.00..6.39 rows=239 width=46) (actual time=0.22..2.90 rows=239 loops=1)
Total runtime: 432543.73 msec
(11 rows)
Please note that since I first posted I have been slightly adjusting the
schema of the tables, but the disparity remains.
Many thanks for your assistance.
Regards,
Harry.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance weirdness with/without vacuum analyze
@ 2003-10-21 12:00 Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
parent: Harry Broomhall <harry.broomhall@uk.easynet.net>
1 sibling, 2 replies; 7+ messages in thread
From: Shridhar Daithankar @ 2003-10-21 12:00 UTC (permalink / raw)
To: Harry Broomhall <harry.broomhall@uk.easynet.net>; pgsql-performance
Harry Broomhall wrote:
> #effective_cache_size = 1000 # typically 8KB each
> #random_page_cost = 4 # units are one sequential page fetch cost
You must tune the first one at least. Try
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these
parameters.
>>2) The EXPLAIN ANALYZE of each query instead of just the EXPLAIN
>
>
> First the case with no vacuum analyze:
>
> QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=99.32..171.32 rows=1000 width=259) (actual
time=18579.92..48277.69 rows=335671 loops=1)
> Merge Cond: ("outer".cdr_id = "inner".cdr_id)
> -> Index Scan using import_cdrs_cdr_id_key on import_cdrs
(cost=0.00..52.00 rows=1000 width=164) (actual time=0.42..11479.51 rows=335671
loops=1)
> -> Sort (cost=99.32..101.82 rows=1000 width=95) (actual
time=18578.71..21155.65 rows=335671 loops=1)
> Sort Key: un.cdr_id
> -> Hash Join (cost=6.99..49.49 rows=1000 width=95) (actual
time=4.70..10011.35 rows=335671 loops=1)
> Hash Cond: ("outer".interim_cli = "inner".interim_num)
> Join Filter: (("outer".starttime >= "inner".starttime) AND
("outer".starttime <= "inner".endtime))
> -> Seq Scan on import_cdrs un (cost=0.00..20.00 rows=1000
width=49) (actual time=0.02..4265.63 rows=335671 loops=1)
> -> Hash (cost=6.39..6.39 rows=239 width=46) (actual
time=4.57..4.57 rows=0 loops=1)
> -> Seq Scan on num_xlate (cost=0.00..6.39 rows=239
width=46) (actual time=0.12..2.77 rows=239 loops=1)
> Total runtime: 80408.42 msec
> (12 rows)
You are lucky to get a better plan here because planner is way off w.r.t
estimated number of rows.
>
> And now the case *with* the vacuum analyze:
>
> QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=15335.91..49619.57 rows=335671 width=202) (actual
time=12383.44..49297.58 rows=335671 loops=1)
> Hash Cond: ("outer".cdr_id = "inner".cdr_id)
> -> Seq Scan on import_cdrs (cost=0.00..8496.71 rows=335671 width=126)
(actual time=0.15..9504.24 rows=335671 loops=1)
> -> Hash (cost=10398.73..10398.73 rows=335671 width=76) (actual
time=12371.13..12371.13 rows=0 loops=1)
> -> Hash Join (cost=6.99..10398.73 rows=335671 width=76) (actual
time=4.91..9412.55 rows=335671 loops=1)
> Hash Cond: ("outer".interim_cli = "inner".interim_num)
> Join Filter: (("outer".starttime >= "inner".starttime) AND
("outer".starttime <= "inner".endtime))
> -> Seq Scan on import_cdrs un (cost=0.00..8496.71
rows=335671 width=30) (actual time=0.09..3813.54 rows=335671 loops=1)
> -> Hash (cost=6.39..6.39 rows=239 width=46) (actual
time=4.71..4.71 rows=0 loops=1)
> -> Seq Scan on num_xlate (cost=0.00..6.39 rows=239
width=46) (actual time=0.22..2.90 rows=239 loops=1)
> Total runtime: 432543.73 msec
> (11 rows)
>
What happens if you turn off hash joins? Also bump sort memory to something
good.. around 16MB and see what difference does it make to performance..
Shridhar
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance weirdness with/without vacuum analyze
@ 2003-10-21 12:35 Harry Broomhall <harry.broomhall@uk.easynet.net>
parent: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
1 sibling, 0 replies; 7+ messages in thread
From: Harry Broomhall @ 2003-10-21 12:35 UTC (permalink / raw)
To: shridhar_daithankar@myrealbox.com; +Cc: harry.broomhall@uk.easynet.net; pgsql-performance
Shridhar Daithankar writes:
> Harry Broomhall wrote:
> > #effective_cache_size = 1000 # typically 8KB each
> > #random_page_cost = 4 # units are one sequential page fetch cost
>
> You must tune the first one at least. Try
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these
> parameters.
Wow. Many thanks for the pointer. I'm going to be spending some time
trying to get my head around all of that!
[SNIP]
> > Total runtime: 80408.42 msec
> > (12 rows)
>
> You are lucky to get a better plan here because planner is way off w.r.t
> estimated number of rows.
Yes! I thought that. Which was why I was so surprised at the difference.
> >
> > And now the case *with* the vacuum analyze:
> >
[SNIP]
>
> What happens if you turn off hash joins? Also bump sort memory to something
> good.. around 16MB and see what difference does it make to performance..
Lots of things to try there.....
It will probably take me some time <grin>.
Regards,
Harry.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance weirdness with/without vacuum analyze
@ 2003-10-21 14:50 Harry Broomhall <harry.broomhall@uk.easynet.net>
parent: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
1 sibling, 0 replies; 7+ messages in thread
From: Harry Broomhall @ 2003-10-21 14:50 UTC (permalink / raw)
To: shridhar_daithankar@myrealbox.com; +Cc: harry.broomhall@uk.easynet.net; pgsql-performance
Shridhar Daithankar writes:
First - many thanks for your suggestions and pointers to further info.
I have been trying some of them with some interesting results!
> Harry Broomhall wrote:
> > #effective_cache_size = 1000 # typically 8KB each
> > #random_page_cost = 4 # units are one sequential page fetch cost
>
> You must tune the first one at least. Try
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these
> parameters.
Changing effective_cache_size seemed to have very little effect. I took it
in steps up to 300MB (the machine has 640MB memory), and the differences
in speed were less than 10%.
[SNIP]
>
> What happens if you turn off hash joins?
This makes the non vacuum version about 40% slower, and the vacuum version
to the same speed (i.e. about 4X faster than it had been!).
> Also bump sort memory to something
> good.. around 16MB and see what difference does it make to performance..
This was interesting. Taking it to 10MB made a slight improvement. Up to
20MB and the vacuum case improved by 5X speed, but the non-vacuum version
slowed down. Putting it up to 40MB slowed both down again.
I will need to test with some of the other scripts and functions I have
written, but it looks as if selective use of more sort memory will be
useful.
Regards,
Harry.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance weirdness with/without vacuum analyze
@ 2003-10-21 17:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Harry Broomhall <harry.broomhall@uk.easynet.net>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-10-21 17:00 UTC (permalink / raw)
To: Harry Broomhall <harry.broomhall@uk.easynet.net>; +Cc: josh@agliodbs.com; pgsql-performance
Harry Broomhall <harry.broomhall@uk.easynet.net> writes:
> -> Index Scan using import_cdrs_cdr_id_key on import_cdrs (cost=0.00..52.00 rows=1000 width=164) (actual time=0.42..11479.51 rows=335671 loops=1)
> -> Seq Scan on import_cdrs (cost=0.00..8496.71 rows=335671 width=126) (actual time=0.15..9504.24 rows=335671 loops=1)
Hm. The planner's default cost parameters assume that a full-table
index scan will be much slower than a full-table seq scan. That's
evidently not the case in your test situation. You could probably
bring the estimates more in line with reality (and thereby improve the
choice of plan) by reducing random_page_cost towards 1 and increasing
effective_cache_size to represent some realistic fraction of your
available RAM (though I concur with your observation that the
latter doesn't change the estimates all that much).
Beware however that test-case reality and production reality are not the
same thing. You are evidently testing with tables that fit in RAM.
If your production tables will not, you'd better be wary of being overly
aggressive about reducing random_page_cost. I believe the default value
(4.0) is fairly representative for situations where many actual disk
fetches are needed, ie, the tables are much larger than RAM. 1.0 would
be appropriate if all your tables are always fully cached in RAM (since
RAM has by definition no random-access penalty). In intermediate cases
you need to select intermediate values.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-10-21 17:00 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-20 16:50 Performance weirdness with/without vacuum analyze Harry Broomhall <harry.broomhall@uk.easynet.net>
2003-10-20 17:28 ` Josh Berkus <josh@agliodbs.com>
2003-10-21 11:40 ` Harry Broomhall <harry.broomhall@uk.easynet.net>
2003-10-21 12:00 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-10-21 12:35 ` Harry Broomhall <harry.broomhall@uk.easynet.net>
2003-10-21 14:50 ` Harry Broomhall <harry.broomhall@uk.easynet.net>
2003-10-21 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
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