Hello, 2009/11/25 Richard Neill <rn...@cam.ac.uk>:
>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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
