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

From: Tom DalPozzo <t(dot)dalpozzo(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: SELECT slow immediately after many update or delete+insert, except using WHERE .. IN
Date: 2016-12-09 16:03:22
Message-ID: CAK77FCQSUPW2PQ6u--yZ4XCOQ1xuOLy84pvgsJusDKSC0ZOWcQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-12-09 16:16:11 Re: SELECT slow immediately after many update or delete+insert, except using WHERE .. IN
Previous Message Merlin Moncure 2016-12-09 15:31:21 Re: storing C binary array in bytea via libpq