From: | Greg Smith <greg(at)2ndquadrant(dot)com> |
---|---|
To: | Ivan Voras <ivoras(at)freebsd(dot)org> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query performance with disabled hashjoin and mergejoin |
Date: | 2011-02-04 14:44:58 |
Message-ID: | 4D4C10EA.2080901@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
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.
--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
From | Date | Subject | |
---|---|---|---|
Next Message | felix | 2011-02-04 14:46:35 | Really really slow select count(*) |
Previous Message | Vitalii Tymchyshyn | 2011-02-04 14:38:30 | Re: [HACKERS] Slow count(*) again... |