SELECT slow immediately after many update or delete+insert, except using WHERE .. IN
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 certain
time and why WHERE..IN is much faster (ok, it's an index, but I'm reading
all the rows).
Regards
Pupillo
2016-12-09 17:16 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
Show quoted text
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.3FIn 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
Pupillo2016-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 usingBEGIN; 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 to9999);
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2016-12-10 15:41 GMT+01:00 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 aBest 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;
Hi,
no I
did not (yet). But I guess that it would be similar to the one or to the
other. I will give updates if I try.
Regards
Pupillo