On 04/02/2011 15:44, Greg Smith wrote:
Ivan Voras wrote:
The "vanilla" plan, with default settings is:
Pause here for a second: why default settings? A default PostgreSQL
configuration is suitable for systems with about 128MB of RAM. Since you
say you have "good enough hardware", I'm assuming you have a bit more
than that. The first things to try here are the list at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; your bad
query here looks like it might benefit from a large increase to
effective_cache_size, and possibly an increase to work_mem as well. Your
"bad" plan here is doing a lot of sequential scans instead of indexed
lookups, which makes me wonder if the change in join types you're
forcing isn't fixing that part as a coincidence.
My earlier message didn't get through so here's a repeat:
Sorry for the confusion, by "default settings" I meant "planner default
settings" not generic shared buffers, wal logs, work memory etc. - which
are adequately tuned.
Note that the estimated number of rows coming out of each form of plan
is off by a factor of about 200X, so it's not that the other plan type
is better estimating anything.
Any ideas how to fix the estimates? Or will I have to simulate hints by
issuing "set enable_hashjoin=f; set enable_mergejoin=f;" for this query? :)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
