On 12/10/2016 04:21 AM, Tom DalPozzo wrote:
Hi, my release is 9.5.4. 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
Best guess, autovacuum kicked in and marked a bunch of rows as no longer in play and thereby reduced the number of rows that needed to be counted.
certain time and why WHERE..IN is much faster (ok, it's an index, but I'm reading all the rows).
So per the second link have you tried something like: SELECT COUNT(*) FROM Table WHERE id > 0;
Regards Pupillo 2016-12-09 17:16 GMT+01:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>>: 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 <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 <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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
