X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5845FD1B509 for ; Tue, 21 Oct 2003 11:41:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 51071-04 for ; Tue, 21 Oct 2003 08:40:36 -0300 (ADT) Received: from haeb.noc.uk.easynet.net (haeb.noc.uk.easynet.net [195.40.7.191]) by svr1.postgresql.org (Postfix) with ESMTP id 8938CD1B4F6 for ; Tue, 21 Oct 2003 08:40:32 -0300 (ADT) Received: (from haeb@localhost) by haeb.noc.uk.easynet.net (8.9.3/8.9.1) id MAA15046; Tue, 21 Oct 2003 12:40:26 +0100 (BST) (envelope-from haeb) Message-Id: <200310211140.MAA15046@haeb.noc.uk.easynet.net> Subject: Re: Performance weirdness with/without vacuum analyze In-Reply-To: <200310201028.00146.josh@agliodbs.com> from Josh Berkus at "Oct 20, 2003 10:28: 0 am" To: josh@agliodbs.com (Josh Berkus) Date: Tue, 21 Oct 2003 12:40:26 +0100 (BST) Cc: harry.broomhall@uk.easynet.net, pgsql-performance@postgresql.org From: Harry Broomhall Reply-To: "Harry Broomhall" X-Mailer: ELM [version 2.4ME+ PL43 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/520 X-Sequence-Number: 4268 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.