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