Thread: [GENERAL] SELECT slow immediately after many update or delete+insert, exceptusing WHERE .. IN

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

In particular:

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@aklaver.com

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@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com