Skip site navigation (1) Skip section navigation (2)

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

From: Sergey Aleynikov <sergey(dot)aleynikov(at)gmail(dot)com>
To: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query times change by orders of magnitude as DB ages
Date: 2009-11-26 12:11:54
Message-ID: a233edb60911260411r7901b12fh1c76d87b3d22178d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Sergey AleynikovDate: 2009-11-26 12:36:16
Subject: Re: Query times change by orders of magnitude as DB ages
Previous:From: Matthew WakelingDate: 2009-11-26 11:14:14
Subject: Re: Query times change by orders of magnitude as DB ages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group