Re: reducing number of ANDs speeds up query RESOLVED

On 15.01.2013, at 05:45, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

>> Which makes me think that, as we grew the database more than 250 times in size over a 2-3 months period, relying on autovacuum (some tables grew from 200k to 50m records, other from 1m to 500m records), the autovacuum has either let us down or something has happen to the ANALYZE.
>
> What do pg_stat_user_tables tell you about last_vacuum, last_autovacuum, last_analyze, last_autoanalyze?

relname | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
------------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
elements | 2013-01-14 16:14:48.963573+00 | | 2013-01-14 16:19:48.651155+00 | 2012-12-12 12:23:31.308877+00

This is the problematic table. I think it is clear. Last autovacuum has been never and last autoanalyze has been mid-December.

Thank you!

>> Is the autovacuum 100% reliable in relation to VACUUM ANALYZE?
>
> No. For example, if you constantly do things that need an access exclusive lock, then autovac will keep getting interrupted and never finish.

I see.

So, apparently, we need to interrupt the heavy imports on some reasonable intervals and do manual VACUUM ANALYZE?

> Cheers,
>
> Jeff

Thank you very much,
T.