Re: query taking much longer since Postgres 8.4 upgrade

Dne 18.3.2011 19:18, Merlin Moncure napsal(a):
> On Fri, Mar 18, 2011 at 10:42 AM, Davenport, Julie <JDavenport(at)ctcd(dot)edu> wrote:
>> This helped, is now down from 14.9 min to 10.9 min to run the entire script. Thanks.
>
> can you try disabling nestloop and see what happens? In the session,
> before running the query, isssue:
> set enable_nestloop = false;

Yes, that'd be interesting. And provide 'exaplain analyze' as before
(using explain.depesz.com) please.

And a bit unrelated recommendation - based on the settings (cost
constants, work_mem etc.) it seems guess you have a default untuned
postgresql.conf. Is that right, Julie? In this case you can
significantly improve the load performance by several settings:

1) increase checkpoint_segments (default is 3, use 12 or something like
that - depends on the if there are warnings about checkpoint segments in
the log)

2) increase wal_buffers (just set it to 16MB and forget it)

The effect depends on the amount of data loaded and other things, but
it's worth a try.

regards
Tomas