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

On 12/09/2016 08:03 AM, Tom DalPozzo wrote:
> Hi,
> I did two tests:
> TEST 1
> 1 I created a table ("Table") with two fields, one ("Id") is a bigint
> and the other ("Data") is a bytea. Also created an index on Id.
> 2 Populated the table with 10000 rows, in which the bigint is
> incremental and bytea is 1000 bytes long.
> 3 Executed SELECT COUNT(*) FROM Table;. ---- It was very fast, almost
> immediate.
> 4 Updated 2000 of those rows for 1000 times. Each time using BEGIN; 2000
> UPDATEs to bytea field (no length changed);COMMIT; <-------- It
> reached around 10000 rows updated/sec.
> 5 Immediately after that, executed SELECT COUNT(*). It took nearly 2
> seconds.
> 6 After 1 minute, executed SELECT COUNT(*). It was immediate again.
>
> TEST 2
> I dropped the table and redid the whole test1 from the beginning but
> using DELETE.. IN (...) + INSERT VALUES (...),(...),...; instead of
> UPDATE at point 4.
> I noticed that:
> - Point 4 took half of the time used through UPDATE (hence now 20000
> rows/sec)-
> - The slowness of SELECT COUNT(*) remained much more than 1 min. (5
> mins?) After that it was fast again.
>
>
> BUT, in both tests, if I substitute point 5 with:
> SELECT * FROM Table WHERE Id IN (0,1,2,... all the numbers up to 9999);
> then it's almost immediate even if executed immediately after point 4
>
> ----

What version of Postgres?

See:

https://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F

In particular:

https://wiki.postgresql.org/wiki/Slow_Counting

> Now the questions:
> I'd like to know the reason of the delay at point 5, in particular in
> the 2nd test and why it is faster when using WHERE..IN .
>
> Also, should I be concerned about the delay at point 5? I mean, my DB
> will receive around 20 millions of updates (or delete+insert) per day.
> Will this delay raise more and more along the months/years?
>
>
> Regards
> Pupillo
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com