Re: small database huge planning time

Hi Alexander

From your EXPLAIN (ANALYZE, BUFFERS), execution is fast because the
very first step is an index scan on _reference127 that returns 0 rows,
so almost all subplans/EXISTS parts are “never executed”. The ~450 ms is
therefore almost entirely /planning/work, not query runtime.

The key clue is “Planning Buffers: shared hit=2717”, which means the
planner is doing lots of catalog/statistics/path exploration in memory
(CPU cost), not waiting on disk. Two likely multipliers in your setup
are (1) join_collapse_limit/from_collapse_limit = 20 (larger join
search/flattening space), and (2) plantuner in shared_preload_libraries
(planner hook overhead). Quick checks: in-session set
join_collapse_limit=1 and from_collapse_limit=1 and compare Planning
Time; then (restart required) temporarily remove plantuner from
shared_preload_libraries and retest. These A/B tests usually identify
whether the overhead is join-search settings or extension hook cost.

Best regards,
[Your Name]

在 2026/1/13 17:16, Alexander Kulikov 写道:
> Hello!
>
>
> I have got huge planning time for a query in quite small database in
> PortgreSQL 17
>  Planning Time: 452.796 ms
>  Execution Time: 0.350 ms
>
> Tried several version from 17.3 to 17.7 (cpu 2.2GHz) - it almost does
> not matter. If I run query many times in row planning time may reduce
> down to 430ms but never less.
>
> Tried in PortgreSQL 11 (in a little bit different hardware with cpu
> 2.60GHz) - planning time almost ten times less.
>
> Changing parameters: from_collapse_limit, join_collapse_limit, geqo,
> jit, work_mem and many others does not help at all. I attach 1.
> additional setting in the postgresql.status.conf. 2. querry itself in
> query.sql. 3. zql plan in query.sqlplan 4. additioanal information
> about os, tables etc. would you please help me -Alexander Kulikov
>
>
>