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
>
>
>