Re: Query times change by orders of magnitude as DB ages

Hello,

2009/11/25 Richard Neill <rn214(at)cam(dot)ac(dot)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