Hello, 2009/11/25 Richard Neill <rn214@xxxxxxxxx>: >It's a simple query, but using a complex view. So I can't really re-order it. View is inserted directly into your query by PG, and then reordered according to from_collapse_limit. Probably, problems lies in the view? How good is it performing? Or from_collapse_limit is _too low_, so view isn't expanded right? >Are you saying that this means that the query planner frequently makes the wrong choice here? Look at explain analyze. If on some step estimation from planner differs by (for start) two order of magnitude from what's really retrieved, then there's a wrong statistics count. But if, on every step, estimation is not too far away from reality - you suffer from what i've described - planner can't reoder efficiently enough query. Because of it happen sometimes - i suspect gego. Or wrong statistics. >I hadn't changed it from the defaults; now I've changed it to: > autovacuum_max_workers = 6 > autovacuum_vacuum_scale_factor = 0.002 > autovacuum_analyze_scale_factor = 0.001 If your tables are not >100mln rows, that's agressive enough. On 100mln rows, this'd analyze table every 100k changed (inserted/updated/deleted) rows. Is this enough for you? Default on large tables are definatly too low. If you get now consistent times - then you've been hit by wrong statistics. Best regards, Sergey Aleynikov -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
