From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: pgsql-bugs@postgresql.org
Subject: Re: db growing out of proportion
Date: Sat, 31 May 2003 12:13:12 -0400
Message-ID: <20748.1054397592@sss.pgh.pa.us> (raw)
In-Reply-To: <20030530225002.5750376f.Robert_Creager@LogicalChaos.org>
References: <Pine.LNX.4.44.0305301012040.22127-100000@RedDragon.Childs>
<8938.1054300299@sss.pgh.pa.us>
<20030530212101.37cef86f.Robert_Creager@LogicalChaos.org>
<17783.1054354286@sss.pgh.pa.us>
<20030530225002.5750376f.Robert_Creager@LogicalChaos.org>
Robert Creager <Robert_Creager@LogicalChaos.org> 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
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: tgl@sss.pgh.pa.us, Robert_Creager@LogicalChaos.org, pgsql-bugs@postgresql.org
Subject: Re: db growing out of proportion
In-Reply-To: <20748.1054397592@sss.pgh.pa.us>
References: <Pine.LNX.4.44.0305301012040.22127-100000@RedDragon.Childs> <8938.1054300299@sss.pgh.pa.us> <20030530212101.37cef86f.Robert_Creager@LogicalChaos.org> <17783.1054354286@sss.pgh.pa.us> <20030530225002.5750376f.Robert_Creager@LogicalChaos.org> <20748.1054397592@sss.pgh.pa.us>
* 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