Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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









[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux