Message-ID: 
 <SA1P221MB099422E6918C0F26F8FF3FDAD58EA@SA1P221MB0994.NAMP221.PROD.OUTLOOK.COM>
Date: Wed, 14 Jan 2026 00:07:33 +0800
User-Agent: Betterbird (Windows)
Subject: Re: small database huge planning time
To: Alexander Kulikov <a-kulikov@hotmail.com>,
 pgsql-performance@postgresql.org
References: 
 <AS4PR02MB874303264FDEF9B160C06DF9EA8EA@AS4PR02MB8743.eurprd02.prod.outlook.com>
Content-Language: en-US
From: Huanbing Lu <luhuanbing@outlook.com>
In-Reply-To: 
 <AS4PR02MB874303264FDEF9B160C06DF9EA8EA@AS4PR02MB8743.eurprd02.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SA1P221MB0994:EE_|DS4PPFC1975104C:EE_
X-MS-Office365-Filtering-Correlation-Id: e566a2b4-1f24-46c7-8b6a-08de52bddc0b
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|5072599009|6090799003|461199028|8060799015|23021999003|15080799012|19110799012|3412199025|440099028|40105399003|52005399003|3430499032;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?MzRQU2dSSlpyWnM2WHpHSGUvdGZqTEN0UFlRYTd4WWlFVVpIbzduRGRjNGcw?=
 =?utf-8?B?bWdMakdKUFhlanB0UHBSQytyUjBLcnpwN0ZsdS94SSt5a0xwOUk0NVVGQkJx?=
 =?utf-8?B?N0hmSXAzM3J3ZVRIK2xML2FzZ0dtR25jMHZNdjM5WmJOVGYvbGRGWGFwWUhC?=
 =?utf-8?B?RXhZUjY3OUFtUFRKMUdrdjVYSE5sMW9ZczRPWElSa3Z6R2ZZQmRXSFdMKzVJ?=
 =?utf-8?B?VG9TOExQeUgwcmRxS0RpNm11dGJLN0NENmgwakZGMWppM0J3eTFLQ3BybDEz?=
 =?utf-8?B?TFJLUy9wd3BhenozZkVsMW9mMjhtK0FBa1drY1h6WnVIK3dtdUlONk55aWdp?=
 =?utf-8?B?TkU3d2dLRjhocDlsbFZUR2N2UkM4Zld5VFdlWTZXNmZ6anpieUhrYitVdDk2?=
 =?utf-8?B?T3FyK1RBc0d2d1o2cHlDV0ozSkpZbGFsS1FCQjRCU1VhaGEzVTZaOUw2eHly?=
 =?utf-8?B?eUp2VDB6cVlVcGNtY2V0K2NCTjZXa1NSZnRVZm9tQ3JjVWh3aVhhUWY4aGZL?=
 =?utf-8?B?dlY2OXV0N3lJR0Z4Ui9hTkN4a2c1M0J0Z2hXSjJLczZhajMwNkVhUms2bzhz?=
 =?utf-8?B?aDJoMmpxL3V1ZUpjcVg3alNFS3pRNzFjdnM1dnJWMm9haTRreGFLL1VEd3Z3?=
 =?utf-8?B?U0VzcXhWaWFhZHZnS2RaL2pJUWY0Tzh0ZG9tSXk0cCtZZE0wQW5DbFBrb1Vn?=
 =?utf-8?B?b3ppT1YvV3A4VVpVdTNlUDJySkNOWDdTQXNHZXZ5SWNCNWphbjF3NWNSVW5x?=
 =?utf-8?B?cnBVOHZScWczbWFwZzVsTCtGVFRIOE52clEyOXhKM2hoa2N1Zy9IUklwaHRj?=
 =?utf-8?B?dUR5N2U0ZXRRcm9hWGdobGxyc3F2RldqLzVOcElXbTZpNUpOOUN6Znd6L1NJ?=
 =?utf-8?B?QmE5MFQ1WlZwTlpHWWxwMFpncFN5MXlKOXhhT2M1V1loZHZjamc4eG9YYmNy?=
 =?utf-8?B?dkZObFVxQkk1aHRZQnhpOUgydkMxUGV1RHA4aG9kcUM0Q0RhK29Xb3ljUlc3?=
 =?utf-8?B?elhDdlQ1c3pyVzIxMHAvSnhiaHlIdnNjL3FkbzllZHd4OGlNdXFrdkdySFJV?=
 =?utf-8?B?b2t4b1VxeHdpMExKa0JFWUxhZW1RdDljK2ROczFqcGRQNERVNTlGTkQyNjBs?=
 =?utf-8?B?RzFJS1h0LzA5aFFVWWpINUxkWUlQeHNBRW1RMUxVQUxucFR0RUlEZHQxdEZD?=
 =?utf-8?B?ZERCK2YwRTVnRzRMa3lEek96WnpyR1JGVkxGZEJqNFNPbVJMd2RRK0ExNWYz?=
 =?utf-8?B?c0Z0L1doVGkvNmEvTGlIYm1BdGhyekwwbnpLazRZYUxtUnlDUUlob3FxbEgz?=
 =?utf-8?B?cWFOeUVlM1NoT1Bjc2wyZkJwdVhjZXZhSHN2T09NZlloSkFENFYzNEpZRUtF?=
 =?utf-8?B?WW1scEo5OTFTTFlUd29MTW5vTnMrUGpqc3BjV29FQWg4cFJUdTVUWUxIS3cz?=
 =?utf-8?B?VnIrSE1VWStTaldqdHAvY3NkNnJERkdpUDhBaW5lc1lYZjZrQUo5TGJqSXpn?=
 =?utf-8?B?QzV4UENSc3d2bVVEQmh3bC9rMmRWQXdNTGQrRENoRG5EakowVk5JM0srU2g3?=
 =?utf-8?B?eVVCbStpU1pvcW91S1V1N2ptLy92NW51WTlYNjlDZ2lSSWRzOHY4bFFhVE1n?=
 =?utf-8?B?WnM2cE9nNGtZSFRJOTlhU0NQNUxBUWc9PQ==?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?c1A0Z3dFckpkYjFIalRlbklocnRmUm5jcC9UeVFVWW9mK0NrcmdzWW9zV2lD?=
 =?utf-8?B?c0xBZnNtTjljOTg0Mk1WZUZYdmluY0tOOHRNanNQVmNQQUdON2hENnA1S2Nx?=
 =?utf-8?B?VTdUWjVWdWwzNDJvdGJmWHF3dk1NY1RXM0o3eEczVVNrZFIxaG45aEZaMVBi?=
 =?utf-8?B?VXcvYXRUZi9EZkM2c1hjR1F4SU8xb2JOaFBud3VNeHdQT1ptL3VQV2h3OWNv?=
 =?utf-8?B?eC9HN2pHenN1aHpwekgrcnNidldMOUdMSHRSdG9lMTMxMElkS1NxZDhrNHpr?=
 =?utf-8?B?Y203SGhIeWJqT0hKR05kZHFxWUVPK3RDM0g1Rkx3QUFBcC91QVR5SDdaa1dx?=
 =?utf-8?B?eElEczEvOFpDQzVDZzVoQWFsTjhJTktNWG5HYVBnOGVLWlEyUi83aitFeGJ6?=
 =?utf-8?B?M1Y5cWJ4NHhsMnJqTGxWcWFKRTRmZGFZVitwQUEyMSt6Wm1zcktmclgzS05H?=
 =?utf-8?B?RWw3MnhyQXlPeXYvenJETjkwdHdNajZsTm9JUWtUbkYxMXRud1FSTWNYTTZ4?=
 =?utf-8?B?TStuaG5GYksycmJ6VTZPRG1nU1FRbzd4QUVTL0tua3JYWk5sdzN5OGFCTm9p?=
 =?utf-8?B?SG1KZURLbEZYejBuelkycjNaSFNsWGw4Q2JUWVJENFhKVkVCQ2dNb2luazJU?=
 =?utf-8?B?emVkTlBHK3J2K1NCYjdHc0xpWUI1ZThYRUtvbVRPYzZYSjRvYzFUU1Nvc1RE?=
 =?utf-8?B?cUFqYWNlK1BxWFB1TU1TVWJTSG1HRU5laDY0OXloZ1hWZkR6djBGWTk3ZHN3?=
 =?utf-8?B?OTlRY2J3by9OWTd1RG80WjdEOTlTckZFQnBXclpKOWJuN2lFQVI3eEpPTVFY?=
 =?utf-8?B?RlUvUnJCdU5EVUNXcUNqK1VVQkZUbkVZZW52NCtnd2Y0ZWpySzhWUlNLelo4?=
 =?utf-8?B?ay85ZWxPYkNCYUVYS0IxRFBDaTJYU05QZW94N01Haks3TDFJME5rYm5BNm4y?=
 =?utf-8?B?SkZ2WFFjTXdBMkhXR3hFcnVWU25VdzVNb0lhR3lvVXRyWjVCM0NpaHh2S3Vj?=
 =?utf-8?B?SGUzR0ZwQ1UydHdmcTRKNmVvMG5qSEx2eTFrdnRDRld4bWlKOVJOVWJpMk1L?=
 =?utf-8?B?UUZLR1Y4a3BvN3hGZVR1MmpPNSthRXRUSnYwdHVoVnNhbTY4Mm1jWFhHdENL?=
 =?utf-8?B?cWUxaHV2eS9CQXB3U0NkcEgrYURiS0RWS3l3dFBrT1BPYlIxVUhVMmpITS9m?=
 =?utf-8?B?R1JGRVhWb1JmUTNDTS9NNFk2WFRxYXRGQVEyTTFENmlRQ1NpRnZaYWFJQndr?=
 =?utf-8?B?WGJBbXo1WUdJQTdHWDVBVUxHYnBHMWZYUk9NMDFBeTZEaVlZcnFxUlBjZnJi?=
 =?utf-8?B?L1d6ZDBudFo5UG16OGJpTXJuYVBmWkRvUmNtTWUxdlk1V3Jady9tTUFpS3dk?=
 =?utf-8?B?YitqZVNTb25qeWw2RjhONzBmeFh5dk5XNmdaU2RYNjlsc3JGaWFMNnVLaGlt?=
 =?utf-8?B?YWpnU0VZNWRyd1NlTmVuNUJSVmhzTm9UcjMxR0lmQmxZaDdBcGpJU1oxSlMx?=
 =?utf-8?B?QmlIZ3NodElCS0tCWE9kNXdPZGtINHcxbFJKbVRkVktjZnRTc1Y1b0pZQkhj?=
 =?utf-8?B?RytFbEJZSUNDMExTL2VIeEYzdFBjK2ZOVGdRbXhQZEdBQnpXV3VCODlCKzRx?=
 =?utf-8?B?SkxwSTN6dm4yZ0kyOTNRNnBjWFcwY08rRmtJZDdJMGVBOTBhUThJQ2h0VHFw?=
 =?utf-8?B?Q21EQ3RyVU15V2NLdXAxQm1ESXpZVVIzTzhJSlZlZHhReit4WDRtY2JWWDhW?=
 =?utf-8?B?Q3VzRThoR016ZUdzLzUzOGlRbjN2VGc3Nit2VXNpTDA5YkVOZk5HR2FzeGhm?=
 =?utf-8?B?ejlkUlVDaEpLbXhGV24rMHlVU3R4ckFOZlphMFVOZFhzTjNlMXBRL0xZWDQz?=
 =?utf-8?Q?Q1bzkZqDlr/BE?=
X-OriginatorOrg: outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 e566a2b4-1f24-46c7-8b6a-08de52bddc0b
X-MS-Exchange-CrossTenant-AuthSource: SA1P221MB0994.NAMP221.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 13 Jan 2026 16:07:33.0125
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DS4PPFC1975104C
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/SA1P221MB099422E6918C0F26F8FF3FDAD58EA%40SA1P221MB0994.NAMP221.PROD.OUTLOOK.COM>
Precedence: bulk

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