X-Original-To: pgsql-bugs@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 24B7B92600C for ; Sat, 31 May 2003 12:13:28 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 95002-07 for ; Sat, 31 May 2003 12:13:17 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id 30B9C926011 for ; Sat, 31 May 2003 12:13:17 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4VGDCU6020749; Sat, 31 May 2003 12:13:12 -0400 (EDT) To: Robert Creager Cc: pgsql-bugs@postgresql.org Subject: Re: db growing out of proportion In-reply-to: <20030530225002.5750376f.Robert_Creager@LogicalChaos.org> References: <8938.1054300299@sss.pgh.pa.us> <20030530212101.37cef86f.Robert_Creager@LogicalChaos.org> <17783.1054354286@sss.pgh.pa.us> <20030530225002.5750376f.Robert_Creager@LogicalChaos.org> Comments: In-reply-to Robert Creager message dated "Fri, 30 May 2003 22:50:02 -0600" Date: Sat, 31 May 2003 12:13:12 -0400 Message-ID: <20748.1054397592@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/128 X-Sequence-Number: 6089 Robert Creager writes: >> I'm interested to narrow down exactly what was the issue here. > shared_buffers was 1024, now 8192 > max_fsm_relations was 1000, now 10000 > max_fsm_pages was 20000, now 100000 > wal_buffers was 8, now 16 > sort_mem was 1024, now 64000 > vacuum_mem was 1024, now 64000 > effective_cache_size was 1000, now 100000 > The query is: > UPDATE obs_v > SET mag = obs_v.imag + zp.zero_v + cg.color_v * (obs_v.imag - i.imag), > use = true > FROM color_group AS cg, zero_pair AS zp, obs_i AS i, files AS f > WHERE obs_v.star_id = i.star_id > AND obs_v.file_id = f.file_id > AND cg.group_id = f.group_id > AND f.group_id = $group_id > AND zp.pair_id = f.pair_id Hm. My best guess is that the increase in sort_mem allowed this query to use a more efficient join plan. Perhaps the planner switched from merge to hash join once it thought the hash table would fit in sort_mem; or maybe the plan didn't change but the executor was able to keep everything in memory instead of using temp files. The other changes you mention seem good as general housekeeping, but I doubt they'd have much direct effect on this query's speed. It'd be interesting to look at EXPLAIN ANALYZE results for the same query at several different sort_mem values. regards, tom lane