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 8FB01D1B53C for ; Tue, 21 Oct 2003 12:00:44 +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 43590-10 for ; Tue, 21 Oct 2003 09:00:15 -0300 (ADT) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id 4B866D1B518 for ; Tue, 21 Oct 2003 09:00:12 -0300 (ADT) Received: from myrealbox.com shridhar_daithankar@smtp-send.myrealbox.com [202.54.11.72] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.42 $ on Novell NetWare via secured & encrypted transport (TLS); Tue, 21 Oct 2003 06:00:18 -0600 Message-ID: <3F951FC8.2090906@myrealbox.com> Date: Tue, 21 Oct 2003 17:30:08 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5b) Gecko/20030917 Thunderbird/0.3a X-Accept-Language: en-us, en MIME-Version: 1.0 To: Harry Broomhall , Performance Subject: Re: Performance weirdness with/without vacuum analyze References: <200310211140.MAA15046@haeb.noc.uk.easynet.net> In-Reply-To: <200310211140.MAA15046@haeb.noc.uk.easynet.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/521 X-Sequence-Number: 4269 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