Re: BUG #17330: EXPLAIN hangs and very long query plans

Поиск
Список
Период
Сортировка
От Strahinja Kustudić
Тема Re: BUG #17330: EXPLAIN hangs and very long query plans
Дата
Msg-id CADKbJJVbqSEv8vL-JCZY7HxPGwX8UQ2rUHs=B0jk0Z9yLtHE4A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #17330: EXPLAIN hangs and very long query plans  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
On Fri, Dec 10, 2021 at 5:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
bloat != corruption.  The problem you're dealing with here is a whole lot
of dead index entries at the very end of the index range (could be either
the lowest or highest entries).  The planner is wasting time scanning
through those to find the extremal non-dead value for a range estimate.
REINDEX got rid of the dead entries; a VACUUM would have done as well.

I completely understand that bloat is different from corruption. It was just really strange that some mass deletes caused this now because we have mass deletes regularly on this DB and it has been running for a few years without any issues. You're right about VACUUM also fixing it, I just tried it inside Docker and after VACUUM finished it instantly fixed the issue. The downside is that VACUUM took a long time to finish.
 
As noted upthread, more recent PG versions are a bit smarter about
such cases.

As far as I understand there is not much we can do except upgrade to future major versions, because on this DB we cannot partition data in that way to be able to drop old partitions.

Thanks again for your help :)

Regards,
Strahinja

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Andres Freund
Дата:
Сообщение: Re: BUG #17321: count(*) on a 1,874,554,883 rows partitioned table takes several minutes.
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: BUG #17255: Server crashes in index_delete_sort_cmp() due to race condition with vacuum