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
