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

On Thu, Dec 9, 2021 at 7:28 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> We had an issue with one of our production databases running Postgres 10.19
> on CentOS 7. One of the most often executed queries started having very long
> query plans of 3000ms+, while the execution duration was 1ms-3ms. Query
> plans when everything is working regularly were around 1ms or less. While on
> the replica it didn't even want to finish EXPLAIN (without ANALYZE, just
> EXPLAIN!). EXPLAIN would just hang forever. To be precise we were running
> 10.10 at that time, but upgrading to 10.19 didn't help. We tried running
> ANALYZE on the whole database, but that didn't help. In the end, what helped
> is running pg_repack on the whole DB. This was strange because I thought
> that the query planner is using table statistics and the index schema to
> determine what plan to run, it shouldn't need table/index data to make a
> plan, but I don't know PG internals, so I might be wrong.

Sounds like a problem with get_actual_variable_range(), which can scan
indexes at plan time to determine minimum or maximum values.

This actually has been improved quite a bit since Postgres 10. So as
Jeff said, seems like you might benefit from upgrading to a newer
major version. v11 has improved things in this exact area.

--
Peter Geoghegan