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

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(dot)klaver(at)aklaver(dot)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
>
> 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(dot)klaver(at)aklaver(dot)com
>