Re: reducing number of ANDs speeds up query RESOLVED

On Monday, January 14, 2013, T. E. Lawrence wrote:

> RESOLVED
> --
> Dear all,
>
> Thank you for your great help and multiple advices.
>
> I discovered the problem and I have to say that it is very stupid and
> strange.
>
> Here is what happened.
>
>
...

> So I decided to try the whole thing properly from the beginning. Dropped
> the index, did again VACUUM ANALYZE and tried the queries, in order to
> measure them without and with index. Surprise! - the slow query was blazing
> fast. The previous indexes (not the dropped partial index) were properly
> used. All was fine.
>
> 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 ?

> 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.

Cheers,

Jeff