Re: [GENERAL] SELECT slow immediately after many update ordelete+insert, except using WHERE .. IN
От | Adrian Klaver |
---|---|
Тема | Re: [GENERAL] SELECT slow immediately after many update ordelete+insert, except using WHERE .. IN |
Дата | |
Msg-id | cd294d6c-d2aa-966b-8b9f-dbd4b5370271@aklaver.com обсуждение исходный текст |
Ответ на | Re: [GENERAL] SELECT slow immediately after many update ordelete+insert, except using WHERE .. IN (Tom DalPozzo <t.dalpozzo@gmail.com>) |
Ответы |
Re: [GENERAL] SELECT slow immediately after many update ordelete+insert, except using WHERE .. IN
(Tom DalPozzo <t.dalpozzo@gmail.com>)
|
Список | 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.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
В списке pgsql-general по дате отправления: