Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w5K08-00388n-0i for pgsql-hackers@arkaria.postgresql.org; Wed, 25 Mar 2026 08:54:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w5K06-00DAk1-19 for pgsql-hackers@arkaria.postgresql.org; Wed, 25 Mar 2026 08:54:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w5K05-00DAjj-30 for pgsql-hackers@lists.postgresql.org; Wed, 25 Mar 2026 08:54:06 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w5K03-00000000wH6-4BSh for pgsql-hackers@postgresql.org; Wed, 25 Mar 2026 08:54:05 +0000 Received: by mail-ej1-x634.google.com with SMTP id a640c23a62f3a-b97bca3797dso275910966b.0 for ; Wed, 25 Mar 2026 01:54:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jeltef.nl; s=google; t=1774428842; x=1775033642; darn=postgresql.org; h=in-reply-to:references:mime-version:from:to:subject:cc:message-id :date:from:to:cc:subject:date:message-id:reply-to; bh=gSa4VKoWA/P5gI2Iu0EwMpsJJ4AMlwMPH9ZeVGD5Vgw=; b=P/2JV304C+zb9G9n9HuOaRHh3NWMd5vlo3nq1xNQ+SmJ2MPbhayAQcMalJewQOaQMg Je2Mr/NDKJ6HhGBe/RLNk5BEyeRELdU+cPDHFZ6RX0jvYCgqBKrdwZD+tThgxMXgEtUk SYJ+FeKQ2VHqKeIhgH7pi4OWRf9MUPt+BbQmP/sP8vLoOFp8JFjePUovsBjVePb44Grt 8Zw0Xps1wKkoHtBJTxZH4MjUsqg2TdZk+H+e30yAx9y0uN71UO6DeNhW0mUfpvfDyDWA +ii0wOP8iAjy5LIt+Qzc1yEDDMTOGzC7KX7Fv2MCHIq6h4aizvu9847IXP1CUK8zB6TR jyDw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1774428842; x=1775033642; h=in-reply-to:references:mime-version:from:to:subject:cc:message-id :date:x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=gSa4VKoWA/P5gI2Iu0EwMpsJJ4AMlwMPH9ZeVGD5Vgw=; b=aCgJlBrmZA1JOeok4ei1iy5DkhzbZBlO3auv/MnB918y5VdvTiX9RTBZo6+ZoWd4wB 71vO+ETpzeKdBP7IEAbPe0Pj3zv4FX/V1iRI4wb5EjoL0w9V8+tBgdSb9sOos0QlKOAb 7rA3uM+qJlyBjVT3lUixgd9a/A2z1NnpNTttB5VyWVZlp1+JbGKoFSWci1YNrTIPmHY7 p2+3eE1F/9n/GP6EEfu75jHdBSseCNh10Vs8iKus8s8+Iye+ryaE/tsLDDAGmLFmb1J/ zjVoHqJurb1AoKoQm7J+tBe5DYtk6rUPN5ZrMi3GpxGlxDJ0YnxYDqSQMPQzKJnDd7l4 gvHw== X-Gm-Message-State: AOJu0YyWQcy3H2Sj+OBplHJEJ/gvEQIwa8FNUkjO6VbzwRhlaq5K6AQK tRcPJ5LYSKvqXPZ81/VL7vlGzvXKtJfFFxSICjMaS9X+kmzAiFsoQH8+rjfC5TfF81M= X-Gm-Gg: ATEYQzx5xdSk9KTIAaqRVn+D3xYrXnoecB0fDOeoOa8R4Hl1zHtTp4EYviftcsP4uGK oyKRFWsX2KkuJcWM5DlLkg35aVV5e0IyxLjtNxCZbP7HHNU/mDMru++VnJ2wBcnDHjKaekQlv7u 4//Mj4E5JwKCYbyqAGgayJ9/+5eorarJFeEnH1SgII7ivXu1hZie+39U2ZjT223za82D1bRs13G ewvbIvssTnmUj5EZB6Dfr+b307TCSZVoRVnYveix/0nVfphS2Oa0hk5ed1BNr2TKYHJBPkGZD9P huPfNoyklVCO9bkzmaUIaUNvr+0U2Hf0/bc6H6I9lyD8mVTm7dAieThGGnlKHj1OsBkKAYUS7n8 WNEpHFMBdtngZ3+x4Fjp3FFd4AQaSsOlmtZZToNrjY83uYK9sEVDidAGhbRdkhyp7pPUbsPygWm x7xmAqZJYlhZulXJLMTwQWsXaAe2D8o1d+aZ4yCw/NEnCJJ0BT X-Received: by 2002:a17:907:97d6:b0:b88:6164:6421 with SMTP id a640c23a62f3a-b9a3f19c2f6mr161520066b.16.1774428842187; Wed, 25 Mar 2026 01:54:02 -0700 (PDT) Received: from localhost (145-53-221-196.fixed.kpn.net. [145.53.221.196]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-b984fcb7e10sm569289766b.11.2026.03.25.01.54.00 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 25 Mar 2026 01:54:01 -0700 (PDT) Content-Type: multipart/mixed; boundary=8fe2c40cd0796ab39687868372ba80f349f2e3e8c9ac420c8bd70adb225f Date: Wed, 25 Mar 2026 09:54:00 +0100 Message-Id: Cc: "PostgreSQL-development" , "Dilip Kumar" , "Thomas Munro" , "Noah Misch" , "Peter Eisentraut" Subject: Make \d tablename fast again, regression introduced by 85b7efa1cdd To: "Andres Freund" From: "Jelte Fennema-Nio" MIME-Version: 1.0 References: In-Reply-To: X-TUID: xwQcs2wNcUrg List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --8fe2c40cd0796ab39687868372ba80f349f2e3e8c9ac420c8bd70adb225f Content-Type: multipart/alternative; boundary=ced1f0b9aaa25e28b5a37771df193a26dffb6fd3899f7f22f0464187836c --ced1f0b9aaa25e28b5a37771df193a26dffb6fd3899f7f22f0464187836c Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Type: text/plain; charset=UTF-8; format=Flowed (forked from: Test timings are increasing too fast for cfbot) On Wed, 25 Mar 2026 at 04:15, Andres Freund wrote: > It seems decidedly not optimal that "\d tablename", without any patterns,= ends > up doing a seqscan. That's bad enough in the regression database, but th= ere > are many PG instances with many many entries in pg_class. > > I don't think this was always the case? > > If I remove the COLLATE pg_catalog.default, a sane plan is chosen. That's > obviously not the right fix, but seemed interesting enough to mention. Due to a very similar problem I faced in the past[1], I thought I had a good sense of where roughly the problem was. And I indeed quickly found it. Attached is a patch that addresses this issue and starts using index scans again for \d tablename. This should be backpatched to PG18 where the regression was introduced by 85b7efa1cdd [1]: https://www.postgresql.org/message-id/flat/CAGECzQRqysy0eJMKR5he3gwtLr= T87f9u5CQQua6B_XNwMnUtFA%40mail.gmail.com --ced1f0b9aaa25e28b5a37771df193a26dffb6fd3899f7f22f0464187836c-- --8fe2c40cd0796ab39687868372ba80f349f2e3e8c9ac420c8bd70adb225f Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename=v1-0001-Fix-LIKE-optimization-for-prefix-scan-with-determ.patch Content-Type: text/x-patch; charset=utf-8; name=v1-0001-Fix-LIKE-optimization-for-prefix-scan-with-determ.patch RnJvbSAwYWI2YmVmMzBhYjBiMTllNDcwNDMyODYyNGNiOTI2ZDA5YTA3ODc2IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBKZWx0ZSBGZW5uZW1hLU5pbyA8cG9zdGdyZXNAamVsdGVmLm5s PgpEYXRlOiBXZWQsIDI1IE1hciAyMDI2IDA5OjI5OjA1ICswMTAwClN1YmplY3Q6IFtQQVRDSCB2 MV0gRml4IExJS0Ugb3B0aW1pemF0aW9uIGZvciBwcmVmaXggc2NhbiB3aXRoIGRldGVybWluaXN0 aWMKIGNvbGxhdGlvbnMKCkluIDg1YjdlZmExY2RkIHN1cHBvcnQgd2FzIGludHJvZHVjZWQgZm9y IExJS0Ugb24gbm9uLWRldGVybWluaXN0aWMKY29sbGF0aW9ucy4gQnkgbW92aW5nIHNvbWUgY29u ZGl0aW9uYWxzIGFyb3VuZCwgdGhhdCBhY2NpZGVudGFsbHkgYnJva2UKdGhlIExJS0Ugb3B0aW1p emF0aW9uIGZvciBkZXRlcm1pbmlzdGljIGNvbGxhdGlvbnMgd2hlbiB0aGUgaW5kZXgKY29sbGF0 aW9uIGRpZCBub3QgbWF0Y2ggdGhlIGZpbHRlciBjb2xsYXRpb24uIFRoaXMgcmUtaW50cm9kdWNl cyB0aGUKb3B0aW1pemF0aW9uLgoKSW1wb3J0YW50IGJlbmVmYWN0b3JzIG9mIHRoaXMgb3B0aW1p emF0aW9uIGFyZSB0aGUgIlxkIHRhYmxlbmFtZSIgYW5kCiJcZCB0YWJsZW5hbWUqIiBjb21tYW5k cyBpbiBwc3FsLiBXaXRob3V0IHRoaXMgb3B0aW1pemF0aW9uIHRoZXkgZG8gYQpzZXF1ZW5jZSBz Y2FuIGluc3RlYWQgb2YgYW4gaW5kZXggbG9va3VwL3ByZWZpeCBzY2FuLgoKRGlzY3Vzc2lvbjog aHR0cHM6Ly9wb3N0Z3IuZXMvbS9tdGtya2tjbjJ0bGh5dHVtaXRwY2g1dWJ4aXBydjJqenZwcmY1 cjVtM21qZWN6dnE0cUBwNndremJmeHV5djIKQmFja3BhdGNoLXRocm91Z2g6IDE4Ci0tLQogc3Jj L2JhY2tlbmQvdXRpbHMvYWR0L2xpa2Vfc3VwcG9ydC5jICB8ICAzICsrLQogc3JjL3Rlc3QvcmVn cmVzcy9leHBlY3RlZC9jb2xsYXRlLm91dCB8IDE4ICsrKysrKysrKysrKysrKysrKwogc3JjL3Rl c3QvcmVncmVzcy9zcWwvY29sbGF0ZS5zcWwgICAgICB8IDExICsrKysrKysrKysrCiAzIGZpbGVz IGNoYW5nZWQsIDMxIGluc2VydGlvbnMoKyksIDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9z cmMvYmFja2VuZC91dGlscy9hZHQvbGlrZV9zdXBwb3J0LmMgYi9zcmMvYmFja2VuZC91dGlscy9h ZHQvbGlrZV9zdXBwb3J0LmMKaW5kZXggMDFjZDZiMTA3MzAuLmRkNjUyNGZjZWM0IDEwMDY0NAot LS0gYS9zcmMvYmFja2VuZC91dGlscy9hZHQvbGlrZV9zdXBwb3J0LmMKKysrIGIvc3JjL2JhY2tl bmQvdXRpbHMvYWR0L2xpa2Vfc3VwcG9ydC5jCkBAIC0zODYsNyArMzg2LDggQEAgbWF0Y2hfcGF0 dGVybl9wcmVmaXgoTm9kZSAqbGVmdG9wLAogCXsKIAkJaWYgKCFvcF9pbl9vcGZhbWlseShlcW9w ciwgb3BmYW1pbHkpKQogCQkJcmV0dXJuIE5JTDsKLQkJaWYgKGluZGV4Y29sbGF0aW9uICE9IGV4 cHJfY29sbCkKKwkJaWYgKGluZGV4Y29sbGF0aW9uICE9IGV4cHJfY29sbCAmJgorCQkJZXhwcl9j b2xsICYmICFnZXRfY29sbGF0aW9uX2lzZGV0ZXJtaW5pc3RpYyhleHByX2NvbGwpKQogCQkJcmV0 dXJuIE5JTDsKIAkJZXhwciA9IG1ha2Vfb3BjbGF1c2UoZXFvcHIsIEJPT0xPSUQsIGZhbHNlLAog CQkJCQkJCSAoRXhwciAqKSBsZWZ0b3AsIChFeHByICopIHByZWZpeCwKZGlmZiAtLWdpdCBhL3Ny Yy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvY29sbGF0ZS5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4 cGVjdGVkL2NvbGxhdGUub3V0CmluZGV4IDI1ODE4ZjA5YWQyLi5jNGNmN2NmYzY0NCAxMDA2NDQK LS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9jb2xsYXRlLm91dAorKysgYi9zcmMvdGVz dC9yZWdyZXNzL2V4cGVjdGVkL2NvbGxhdGUub3V0CkBAIC03NjgsNiArNzY4LDI0IEBAIERFVEFJ TDogIExPQ0FMRSBjYW5ub3QgYmUgc3BlY2lmaWVkIHRvZ2V0aGVyIHdpdGggTENfQ09MTEFURSBv ciBMQ19DVFlQRS4KIENSRUFURSBDT0xMQVRJT04gY29sbF9kdXBfY2hrIChGUk9NID0gIkMiLCBW RVJTSU9OID0gIjEiKTsKIEVSUk9SOiAgY29uZmxpY3Rpbmcgb3IgcmVkdW5kYW50IG9wdGlvbnMK IERFVEFJTDogIEZST00gY2Fubm90IGJlIHNwZWNpZmllZCB0b2dldGhlciB3aXRoIGFueSBvdGhl ciBvcHRpb25zLgorLS0gUmVnZXggZXhhY3QtbWF0Y2ggb3B0aW1pemF0aW9uIHNob3VsZCB1c2Ug aW5kZXggZXZlbiB3aGVuIHRoZSBleHByZXNzaW9uCistLSBoYXMgQ09MTEFURSAiZGVmYXVsdCIg YW5kIHRoZSBpbmRleCBoYXMgYSBkaWZmZXJlbnQgKGJ1dCBkZXRlcm1pbmlzdGljKQorLS0gY29s bGF0aW9uIE9JRCwgYmVjYXVzZSBlcXVhbGl0eSBpcyBjb2xsYXRpb24taW5zZW5zaXRpdmUgZm9y IGRldGVybWluaXN0aWMKKy0tIGNvbGxhdGlvbnMuCitDUkVBVEUgVEFCTEUgY29sbGF0ZV90ZXN0 cy5yZWdleF9pZHhfdGVzdCAoeCB0ZXh0KTsKK0NSRUFURSBJTkRFWCBPTiBjb2xsYXRlX3Rlc3Rz LnJlZ2V4X2lkeF90ZXN0ICh4IENPTExBVEUgIkMiKTsKK1NFVCBlbmFibGVfc2Vxc2NhbiA9IG9m ZjsKK0VYUExBSU4gKGNvc3RzIG9mZikgU0VMRUNUICogRlJPTSBjb2xsYXRlX3Rlc3RzLnJlZ2V4 X2lkeF90ZXN0IFdIRVJFIHggfiAnXihhYmMpJCcgQ09MTEFURSAiZGVmYXVsdCI7CisgICAgICAg ICAgICAgICAgICAgUVVFUlkgUExBTiAgICAgICAgICAgICAgICAgICAgCistLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgQml0bWFwIEhlYXAgU2NhbiBv biByZWdleF9pZHhfdGVzdAorICAgRmlsdGVyOiAoeCB+ICdeKGFiYykkJzo6dGV4dCkKKyAgIC0+ ICBCaXRtYXAgSW5kZXggU2NhbiBvbiByZWdleF9pZHhfdGVzdF94X2lkeAorICAgICAgICAgSW5k ZXggQ29uZDogKHggPSAnYWJjJzo6dGV4dCkKKyg0IHJvd3MpCisKK1JFU0VUIGVuYWJsZV9zZXFz Y2FuOworRFJPUCBUQUJMRSBjb2xsYXRlX3Rlc3RzLnJlZ2V4X2lkeF90ZXN0OwogLS0KIC0tIENs ZWFuIHVwLiAgTWFueSBvZiB0aGVzZSB0YWJsZSBuYW1lcyB3aWxsIGJlIHJlLXVzZWQgaWYgdGhl IHVzZXIgaXMKIC0tIHRyeWluZyB0byBydW4gYW55IHBsYXRmb3JtLXNwZWNpZmljIGNvbGxhdGlv biB0ZXN0cyBsYXRlciwgc28gd2UKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2Nv bGxhdGUuc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY29sbGF0ZS5zcWwKaW5kZXggNGIwZTQ0 NzJjM2YuLjRmNGY2MDdjNzBlIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9jb2xs YXRlLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9jb2xsYXRlLnNxbApAQCAtMzAyLDYg KzMwMiwxNyBAQCBDUkVBVEUgQ09MTEFUSU9OIGNvbGxfZHVwX2NoayAoTENfQ1RZUEUgPSAiUE9T SVgiLCBMT0NBTEUgPSAnJyk7CiAtLSBGUk9NIGNvbmZsaWN0cyB3aXRoIGFueSBvdGhlciBvcHRp b24KIENSRUFURSBDT0xMQVRJT04gY29sbF9kdXBfY2hrIChGUk9NID0gIkMiLCBWRVJTSU9OID0g IjEiKTsKIAorLS0gUmVnZXggZXhhY3QtbWF0Y2ggb3B0aW1pemF0aW9uIHNob3VsZCB1c2UgaW5k ZXggZXZlbiB3aGVuIHRoZSBleHByZXNzaW9uCistLSBoYXMgQ09MTEFURSAiZGVmYXVsdCIgYW5k IHRoZSBpbmRleCBoYXMgYSBkaWZmZXJlbnQgKGJ1dCBkZXRlcm1pbmlzdGljKQorLS0gY29sbGF0 aW9uIE9JRCwgYmVjYXVzZSBlcXVhbGl0eSBpcyBjb2xsYXRpb24taW5zZW5zaXRpdmUgZm9yIGRl dGVybWluaXN0aWMKKy0tIGNvbGxhdGlvbnMuCitDUkVBVEUgVEFCTEUgY29sbGF0ZV90ZXN0cy5y ZWdleF9pZHhfdGVzdCAoeCB0ZXh0KTsKK0NSRUFURSBJTkRFWCBPTiBjb2xsYXRlX3Rlc3RzLnJl Z2V4X2lkeF90ZXN0ICh4IENPTExBVEUgIkMiKTsKK1NFVCBlbmFibGVfc2Vxc2NhbiA9IG9mZjsK K0VYUExBSU4gKGNvc3RzIG9mZikgU0VMRUNUICogRlJPTSBjb2xsYXRlX3Rlc3RzLnJlZ2V4X2lk eF90ZXN0IFdIRVJFIHggfiAnXihhYmMpJCcgQ09MTEFURSAiZGVmYXVsdCI7CitSRVNFVCBlbmFi bGVfc2Vxc2NhbjsKK0RST1AgVEFCTEUgY29sbGF0ZV90ZXN0cy5yZWdleF9pZHhfdGVzdDsKKwog LS0KIC0tIENsZWFuIHVwLiAgTWFueSBvZiB0aGVzZSB0YWJsZSBuYW1lcyB3aWxsIGJlIHJlLXVz ZWQgaWYgdGhlIHVzZXIgaXMKIC0tIHRyeWluZyB0byBydW4gYW55IHBsYXRmb3JtLXNwZWNpZmlj IGNvbGxhdGlvbiB0ZXN0cyBsYXRlciwgc28gd2UKCmJhc2UtY29tbWl0OiBjNzllNDE0MTI3M2Nh YTFiNGZiODhjNDc5YmI5MGRjNDBmMmZiYmYyCi0tIAoyLjUzLjAKCg== --8fe2c40cd0796ab39687868372ba80f349f2e3e8c9ac420c8bd70adb225f--