Re: db growing out of proportion

Robert Creager <Robert_Creager(at)LogicalChaos(dot)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