Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Tom DalPozzo]

2016-12-10 15:41 GMT+01:00 Adrian Klaver :

> 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;
>
>
>> ​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

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Adrian Klaver]

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 >:

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 1 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 1 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  2
> 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
);
> 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.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Tom DalPozzo]

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 :

> 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 1 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 1 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  2
> > 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 );
> > 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.kla...@aklaver.com
>

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Adrian Klaver]

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 1 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 1 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  2
> 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 );
> 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.kla...@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