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

Поиск
Список
Период
Сортировка
От Strahinja Kustudić
Тема Re: BUG #17330: EXPLAIN hangs and very long query plans
Дата
Msg-id CADKbJJVB7okJa8m_XVsYzBy48Cny+tVv5wXcbUZWA_cw_o7nzg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #17330: EXPLAIN hangs and very long query plans  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
On Fri, Dec 10, 2021 at 2:31 AM Jeff Janes <jeff.janes@gmail.com> wrote:
On Thu, Dec 9, 2021 at 10:28 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17330
Logged by:          Strahinja Kustudic
Email address:      strahinjak@nordeus.com
PostgreSQL version: 10.19
Operating system:   CentOS 7
Description:       
 
 The issue is that in Docker EXPLAIN runs forever, it never
finishes, like the issue we had on the replica. This means I have a 100%
repro of the issue and I can do whatever you tell me to do to find out what
caused it.

Is this 100% CPU bound?  'perf top -p <pid>' would probably be my first tool to use here.

I forgot to mention this important detail. When it happened on the production DB it was CPU bound. It was about 70% user time and 30% system time. There was no iowait because most of the DB was in page cache.

Check out the screenshot of the CPU usage on the production DB in the attachment. Here is the timeline:
- 11:00 we started deleting a few million rows
- 11:34 delete finished and instantly CPU went to ~70% user and ~30% system time
- 12:55 we configured PgBouncer to only allow just a few queries in parallel when system time went down, but user time was still 100%

On my Docker instance when I execute EXPLAIN it starts reading a lot of data. The indexes of the biggest table the query reads are 50GB, so my guess is that it reads those indexes.

I also allowed EXPLAIN in Docker to finish and it took almost 500 seconds. Can the index corruption cause this?

Regards,
Strahinja
Вложения

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

Предыдущее
От: "James Pang (chaolpan)"
Дата:
Сообщение: RE: BUG #17326: Postgres crashed when pg_reload_conf() with ssl certificate parameters
Следующее
От: Strahinja Kustudić
Дата:
Сообщение: Re: BUG #17330: EXPLAIN hangs and very long query plans