--
Sent from my Android phone, please excuse my brevity.
Greg Smith <greg@xxxxxxxxxxxxxxx> 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. 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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
