Re: reducing number of ANDs speeds up query RESOLVED

On Tue, Jan 15, 2013 at 7:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "T. E. Lawrence" <t(dot)e(dot)lawrence(at)icloud(dot)com> <CAMkU=1y6UuxPYbf_ky8DVDsJi=g=uQ1t0B6kwLEtdc7NLxB_-Q(at)mail(dot)gmail(dot)com> writes:
>> On 15.01.2013, at 05:45, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>> 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?
>
> Data import as such, no matter how "heavy", shouldn't be a problem.
> The question is what are you doing that takes access-exclusive table
> locks frequently, and do you really need to do that?
>
> A quick look at the docs suggests that ALTER TABLE, REINDEX, or CLUSTER
> would be the most likely candidates for taking exclusive table locks.

But that isn't an exhaustive list--weaker locks will also cancel
autovacuum, for example I think the SHARE lock taken by CREATE INDEX
will and the even weaker one taken by CREATE INDEX CONCURRENTLY will
too.

But will all of those cancel auto-analyze as well as auto-vac? I
guess they will because they use the same lock level.

T.E., Fortunately in point releases from August 2012 (9.0.9, 9.1.5,
etc.), the default server log settings will log both the cancel and
the command triggering the cancel. So if you are running an up to
date server, you can just look in the logs to see what is happening.

Cheers,

Jeff