From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tom DalPozzo <t(dot)dalpozzo(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SELECT slow immediately after many update or delete+insert, except using WHERE .. IN |
Date: | 2016-12-10 14:41:33 |
Message-ID: | cd294d6c-d2aa-966b-8b9f-dbd4b5370271@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
> <mailto: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
> <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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2016-12-10 15:04:42 | Re: huge table occupation after updates |
Previous Message | Tom DalPozzo | 2016-12-10 14:25:54 | Re: huge table occupation after updates |