From: Harry Broomhall <harry.broomhall@uk.easynet.net>
To: josh@agliodbs.com
Cc: harry.broomhall@uk.easynet.net
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance weirdness with/without vacuum analyze
Date: Tue, 21 Oct 2003 12:40:26 +0100 (BST)
Message-ID: <200310211140.MAA15046@haeb.noc.uk.easynet.net> (raw)
In-Reply-To: <200310201028.00146.josh@agliodbs.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: harry.broomhall@uk.easynet.net, josh@agliodbs.com
Subject: Re: Performance weirdness with/without vacuum analyze
In-Reply-To: <200310211140.MAA15046@haeb.noc.uk.easynet.net>
References: <200310201028.00146.josh@agliodbs.com> <200310211140.MAA15046@haeb.noc.uk.easynet.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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