From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | "T(dot) E(dot) Lawrence" <t(dot)e(dot)lawrence(at)icloud(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: reducing number of ANDs speeds up query RESOLVED |
Date: | 2013-01-15 04:45:18 |
Message-ID: | CAMkU=1y6UuxPYbf_ky8DVDsJi=g=uQ1t0B6kwLEtdc7NLxB_-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Abel Abraham Camarillo Ojeda | 2013-01-15 05:26:51 | Re: INSERT... WHERE |
Previous Message | ning chan | 2013-01-15 02:05:19 | Streaming Replication |