Re: SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

2016-12-10 15:41 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 12/10/2016 04:21 AM, Tom DalPozzo wrote:
>
>> Hi,
>> my release is 9.5.4.
>> a took a look over it. I guessed that counting could be slow because it
>> needs to read everything and also that it can take advantage from an
>> index. But I don't understand why the delay is after the updates for a
>>
>
> Best guess, autovacuum kicked in and marked a bunch of rows as no longer
> in play and thereby reduced the number of rows that needed to be counted.
>
> certain time and why WHERE..IN is much faster (ok, it's an index, but
>> I'm reading all the rows).
>>
>
> So per the second link have you tried something like:
>
> SELECT COUNT(*) FROM Table WHERE id > 0;
>
>
>> ​Hi,
no I ​

​did not (yet). But I guess that it would be similar to the one or to the
other. I will give updates if I try.
Regards
Pupillo