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 certain time and why WHERE..IN is much faster (ok, it's an index, but I'm reading all the rows).
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 > > ----
> 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 > > > > > > > >