public inbox for [email protected]
help / color / mirror / Atom feedFrom: Huanbing Lu <[email protected]>
To: Alexander Kulikov <[email protected]>
To: [email protected]
Subject: Re: small database huge planning time
Date: Wed, 14 Jan 2026 00:07:33 +0800
Message-ID: <SA1P221MB099422E6918C0F26F8FF3FDAD58EA@SA1P221MB0994.NAMP221.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <AS4PR02MB874303264FDEF9B160C06DF9EA8EA@AS4PR02MB8743.eurprd02.prod.outlook.com>
References: <AS4PR02MB874303264FDEF9B160C06DF9EA8EA@AS4PR02MB8743.eurprd02.prod.outlook.com>
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
>
>
>
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: small database huge planning time
In-Reply-To: <SA1P221MB099422E6918C0F26F8FF3FDAD58EA@SA1P221MB0994.NAMP221.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox