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.94.2) (envelope-from ) id 1uNCjI-00EBUR-A1 for pgsql-performance@arkaria.postgresql.org; Thu, 05 Jun 2025 15:42:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uNCjG-00AwTd-78 for pgsql-performance@arkaria.postgresql.org; Thu, 05 Jun 2025 15:42: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.94.2) (envelope-from ) id 1uNCjF-00AwRb-I5 for pgsql-performance@lists.postgresql.org; Thu, 05 Jun 2025 15:42:06 +0000 Received: from mx0b-001b2d01.pphosted.com ([148.163.158.5]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uNCjE-000PUe-0i for pgsql-performance@lists.postgresql.org; Thu, 05 Jun 2025 15:42:05 +0000 Received: from pps.filterd (m0353725.ppops.net [127.0.0.1]) by mx0a-001b2d01.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 555DVNlD008837 for ; Thu, 5 Jun 2025 15:42:03 GMT DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ibm.com; h= content-type:date:from:message-id:mime-version:subject:to; s= pp1; bh=wkCerybBc3hVGk9IZBYTRBHamvc64kzam24Wry+WT/s=; b=OzZUVphA wcUf3u3zhUXFjGz5EsX1xdHK1mli3N95GPE+X68z/QW5d5qFn/fBU7/gcZz/XUVi nrkIMoxzTwDteTRmdEVuvj/jrn9/5pyck4+1p7hDXXCBCP+OtUYKLdxca6yp/1bD kTQ7Bw2Aq5lhKIN6K3l1SBKe91KWKLbcW8DPbwJJmEFh0W5lg0vl94irY/PZ1LVd dsoflwIBMgODol2dfFRmcX1BgDdnfr1lxDcBTynIsxudKzR0TejY8qZiTixj9ctx 5ZEQjB18cYU34BPdTBlcaLgt+EWSzYEa9LruoA20XmUEad8YqYvbtwW8KZuCsHwQ AmeI5VGLwZVNrQ== Received: from nam04-dm6-obe.outbound.protection.outlook.com (mail-dm6nam04on2040.outbound.protection.outlook.com [40.107.102.40]) by mx0a-001b2d01.pphosted.com (PPS) with ESMTPS id 47332yk9sx-2 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Thu, 05 Jun 2025 15:42:03 +0000 (GMT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=VHhtMHifCsU6kKF7W/vh7O4+zAn4/9iZWof6l1gP4e9rln4zqOPFGGj/cL5jzPiCvmmIVSlAi6qw5riuhvNXVTI/jz7Kzicpyq5VWaOIaAsMiP7ZXLvKDIWAeHk34YJUdDyeaIB0e7JXzv36UKmbfu2BehjKARYPFiVma6lvTyPXUlCh78aDmPtVbhHP/9izdqgVv7uKqp0ZNdVqHn1MXPdiEsuPDlhG4ciPL2BbvGFXanKZjKH4Gd2Bm0UUrUAEb16UbvLHWOBHZA/nuMnoBl+4FNqusIbLdWJuDcLheBRY82d74aZgUNLbom8OAqwRCdQQgLrm74e5nORH+GexVQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=0iI/GDDeIxQfnc/QjPxPYpZBoOQp1rX73eIUA4k7y0I=; b=UY8yuNBUwsfrE+FAy96MjtFZhXnqnZXf/gQxFf8RtKZ3u0p7cBBaXIHDfqMNfexWJn6SRYUE5vOGiqsFhPWJOpNUg2fncY1Yiipuwc/7UyfnE+6Ao1WYiRnJyZtt1knrVIWJaOR98P9PdjQtz2T31VPeVTPwmkVGKXG0Thd13FueWiZDpQV8Sgk5Xz1Bvx5O8/AqhIMfmlCeMOmEdjT/MnJiJskVdgCXoldUdmTQfNJYrh6rG83ZkrPl1HMlSTq7MYaKTxF9Ly8beKuVGLroqruNZf1sXZr/vp83xCqNxjVWDbEMO9byZFGvxj20X1n+46ae7qgC3+wLBT/9T2c7zQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=uk.ibm.com; dmarc=pass action=none header.from=uk.ibm.com; dkim=pass header.d=uk.ibm.com; arc=none Received: from PH3PPF1C905D6E6.namprd15.prod.outlook.com (2603:10b6:518:1::48a) by DM4PR15MB5356.namprd15.prod.outlook.com (2603:10b6:8:5f::22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8813.19; Thu, 5 Jun 2025 15:42:00 +0000 Received: from PH3PPF1C905D6E6.namprd15.prod.outlook.com ([fe80::97ec:26dc:d62e:daef]) by PH3PPF1C905D6E6.namprd15.prod.outlook.com ([fe80::97ec:26dc:d62e:daef%6]) with mapi id 15.20.8813.018; Thu, 5 Jun 2025 15:42:00 +0000 From: Mark Frost To: "pgsql-performance@lists.postgresql.org" Subject: Poor row estimates from planner, stat `most_common_elems` sometimes missing for a text[] column Thread-Topic: Poor row estimates from planner, stat `most_common_elems` sometimes missing for a text[] column Thread-Index: AQHb1ikCQw3lZBlZCEqDxo53AemQGg== Date: Thu, 5 Jun 2025 15:42:00 +0000 Message-ID: Accept-Language: en-GB, en-US Content-Language: en-GB X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-reactions: allow x-ms-publictraffictype: Email x-ms-traffictypediagnostic: PH3PPF1C905D6E6:EE_|DM4PR15MB5356:EE_ x-ms-office365-filtering-correlation-id: e2289953-cf23-42b5-c21b-08dda4478312 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|366016|376014|1800799024|8096899003|38070700018; x-microsoft-antispam-message-info: =?Windows-1252?Q?aHuCIIrhlKKbrY4iQnNOg7XFso3QePe4spMIqBV/Rm5hyIPqNTjyYp0N?= =?Windows-1252?Q?8A4A7ti1go+6TbhdNnuLNAGYY4AtBu3ZuZcj6+RwN7LSdZ7rk1HjICn3?= =?Windows-1252?Q?NwAZfxeCpyKBtMawqx1GxRauk1EFguFhZayUnpjbf9XJqL8+/EF+oejb?= =?Windows-1252?Q?gXZXzBvVQfjsMPHmQTDhyDPGmr57sOjwpNh7/tzjv8T+FnBYGR0zRAox?= =?Windows-1252?Q?qMccGhOfZc8DVQuySZhcg7lELMFa+j5b4X3OQt9gh1x8pzRkE6csNiXM?= =?Windows-1252?Q?goYi7aU2ZgkwwhfgqBKOL9GNDQxjm2uWZcUZOaMZEjIAy0S37k1V+osX?= =?Windows-1252?Q?fO2ypTsLzXR8FqBaCB2Eed5qiNKApMW20YagevMzd8kgcWVdJaue4CAm?= =?Windows-1252?Q?cRhDu/KGIkr4EjsrG8A8RPUqJPS848I5TWD/fnarlPJlcrlGef26KCs4?= =?Windows-1252?Q?bSm46yUBesz8awMfmko4Oq0p6CxB+wJABrZycutrYkcqxnpsmIguJFX5?= =?Windows-1252?Q?ORugXBUXP/yxkXZ3ElwE8H1Ah0+dQHqSbNcDcduSjQHTGi93TkSNiPHZ?= =?Windows-1252?Q?XPdn/3oOE+zeGjeDRy7zO1ZxaXm29+a85rf7E4jEd+BzBLYGz8og0HA7?= =?Windows-1252?Q?I6cpldtCycfVKBvVS9VQvo3QOwb3DXRkZqRGgo7edY7r2b8KH1AEvrHs?= =?Windows-1252?Q?h6bQXZ6kEdKLH5gPslNstmauWlcVyVkQwOta0dzMrVh3Cly7m7DcSIGv?= =?Windows-1252?Q?PtZWJcQszi87dz29ur8L4/6wJaSw2umQskYA6tWF03NB/0tqb1Aaq6V1?= =?Windows-1252?Q?1B5ZSjCgSE1x0akTreYjAzEY6wm+F8REvJWSXqWJqHbzTLNqrkzL3tyi?= =?Windows-1252?Q?FPwhH96YBoyN8OUlbnPtMvCdRHG1stS4v5H6Z+RSZqLve8EX1GI1zJ9N?= =?Windows-1252?Q?pGzwyhbr/QdXjD6GQw2lCnHzXVCn2siQ98b8ShVg79mPeUwV53mD800y?= =?Windows-1252?Q?5R0DmkIBbHkKDw45gPPF1lpxoVNxtzMOqWHUgAYe3Si9J1at5bN8EZ3D?= =?Windows-1252?Q?qsQSB80h5AIrHtpm4yYn45hjGJOwoGR98kqVLEn2EdQ4ngAXWCG2JLIx?= =?Windows-1252?Q?zOIpXSljfs/UHEGXJZuliu5+LWxUibP900W9ScBn073dHYDEgyPPQQii?= =?Windows-1252?Q?GZJSkNG1mvByh68mOV7KE11yqTwmra5nEMxvV2UVlY3l8JRhWk1xXKRZ?= =?Windows-1252?Q?42aWTMpE0sHt6wT77W9E/xfVAM72k3pn+PxpnDgVD+wAt1tqmNrYVks0?= =?Windows-1252?Q?Y++6y/IWWK7EynSr3suCMXw3o4hq57wfg0y3Ta0O0hWxOuhaiU3f7Q8t?= =?Windows-1252?Q?QTZ6yos9GKO07ZVCVVlemsPeEvv2Kn1H03yA844vywVYt7h2T15/K8Mc?= =?Windows-1252?Q?w3bE4JsbUzGUzON3gdAxcubmDJsskGQuhyX0YxqylPlVcGL95pQAE0jR?= =?Windows-1252?Q?zlzL1qsHMCBnuMtzSsOHqj358ALnrfnWJeONDg5fGpUfo10/oj6oQsrh?= =?Windows-1252?Q?GkXsmE+AIW9WEgOBA9SKIUayyVFUIkjkpJtt5A=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:PH3PPF1C905D6E6.namprd15.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(366016)(376014)(1800799024)(8096899003)(38070700018);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?/cTzpO/c94LZawcYGY/JFDnKnoeojsC00bLK6L/3BOJajaRbmyaHD+Bz?= =?Windows-1252?Q?w3ptu7iPpQIO9skeX3XkxYh2cRdtLnJqeeqggU6OTYHU+scpezH8JXJT?= =?Windows-1252?Q?mRT85+WlbMhfor0y9mVZilDdIsJC3DTZbE4hrGFcWT90EAbX2Vo/Zaf8?= =?Windows-1252?Q?yLzgo9sbSg/OKRcYxdB44YdaGGCmuSKsL3+AiZHXq6DZBJQ5FZznbrm6?= =?Windows-1252?Q?hp/xHn8kALvJwZ76LT5VXEh1+E9rCnDL6MV6SKu+G5Dq2zIKsE8h7aqK?= =?Windows-1252?Q?IMrD3Sebv37gnOLEqWVD0L3R//RoDU5N10tsVThIgcJJhnSrDTasMlWw?= =?Windows-1252?Q?+yAg488QKaBq7g7eB8ZnCKDoOx8o7FeDE6PP/jFskQ3+Hng7UA0CrA74?= =?Windows-1252?Q?lXeDY9PlY2ldetgnATglTQkZJa6WJzJjOp1ci4nt4VFyvncH9gTnOqaf?= =?Windows-1252?Q?JRbMkzxtgZio5sWx3dbdPAoj0AHdd0toyaQ9g89+AvhYJhqDmQe6jgWF?= =?Windows-1252?Q?DMEa+yYLLYU/v1GcT9dRZ5+NwHBC5bRvSjWFqGM+L3+KRXoGc3m1dBCW?= =?Windows-1252?Q?ZbzgHi/9h7uRPD3tSNtGvUQV2PeM+Z93dx3N7/oJvvD/DYaghnsCgOcH?= =?Windows-1252?Q?DEdQ9aE51JoacPOLp73e5spWdzE7ZMZGlDtvx91dS1e4p0HAacaRnZ9B?= =?Windows-1252?Q?lRWOIl0Occ07IG7Km4IS4oG9empvM0vRK0aQqvsYEfMVrzwIMmZoeanb?= =?Windows-1252?Q?KeEwp+Hb0XWVLjI0/EU3EeQfzqD0ygVqSdYTOH5elSE2gMUM0N/MyFtL?= =?Windows-1252?Q?ZvwmH140RbCq6mPuPxu6P/uxVblOcRCGXg3td1leCxglgiZWy4y1AtFW?= =?Windows-1252?Q?cI4w/z2ePQN4fjWyHNVJOiiifJzrnqmYjs6r6PbTcef3bn5bLLZrhOFt?= =?Windows-1252?Q?+EFiIPb5B4u+3+toYzY8J8uGOOK1UF5OQFaytrEVXiWo9U3FlJsk4W0j?= =?Windows-1252?Q?Ki3Q4vrCkE8PmX2h62B7KQkN9pJTt/xVGq5c8Z+6XrJRd4Qe84TasXq/?= =?Windows-1252?Q?Wd553Eg0E+CHfE/Jm5m98PwiiQM4KnsyVAAVWdldOy7zjR4usXfLcpGt?= =?Windows-1252?Q?qw2IKpOgWeQozZ+r7Sq9Ho1D1H/N2JNrHSWcLeDOM51VgBHxsmt3yVO+?= =?Windows-1252?Q?CfUGHb7fwGIL6n1gG2UNaEv2j9m19bW25Bf3flHSmErJKU9q3Tlop8zg?= =?Windows-1252?Q?D0RshMvNGDI2vSk1LKxWSWlEXQLeED+O/ILZEsMdBRTks776NxmIwEko?= =?Windows-1252?Q?0m8EC5q1LOPIpO3xVhIIpxHHp7oQsnDtDVfTbA0mh5Uh5+awavtCFDHu?= =?Windows-1252?Q?JreaPT+jqRr4lUDnkfFDi0KgWTHloQ4jGG3Hh8vYjjIyHfSPIoHjMFot?= =?Windows-1252?Q?6x76fDKLeieP2gusfDL+j87x8aVaIxzOrEU+hr4ondfiRkXx509k+uwu?= =?Windows-1252?Q?VABmL0RUSsSFY531awAV1DTF6sWJmkHuJKqfxjZoKo5fWM0Yosvg3HY9?= =?Windows-1252?Q?r1uJkEKcPUjCEcuOKl0WxLr4GQKqSGlUy1aYjn5fYrDI6jJwZOhico5O?= =?Windows-1252?Q?Mu8PkuDwTaUMHUmuk3m6mDRiDUYNUOvpfM0TDCLOZK2dpa8pA1oqHByb?= =?Windows-1252?Q?e+zH1qVvL0YKgFTI5HrlpP5AxhE+HnQvNMj4rnPbNQvLAsTYKwd7Ig?= =?Windows-1252?Q?=3D=3D?= Content-Type: multipart/alternative; boundary="_000_PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FAPH3PPF1C905D6E6_" MIME-Version: 1.0 X-OriginatorOrg: uk.ibm.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: PH3PPF1C905D6E6.namprd15.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: e2289953-cf23-42b5-c21b-08dda4478312 X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Jun 2025 15:42:00.5032 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: fcf67057-50c9-4ad4-98f3-ffca64add9e9 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: 0S63RThn9aU/8zp87L5y43gSlu0+muMbKBD73BYYDDYA4O9nmd53xvNe+OozgubVCWwcthdzkw2m5Xqwc/yYiA== X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM4PR15MB5356 X-Proofpoint-GUID: PjPC8fXYbqK39a-d11aSEToMT-DOJZmd X-Proofpoint-ORIG-GUID: PjPC8fXYbqK39a-d11aSEToMT-DOJZmd X-Authority-Analysis: v=2.4 cv=SO9CVPvH c=1 sm=1 tr=0 ts=6841bacb cx=c_pps a=YRACufkBk+Nn0Aes1d57Eg==:117 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19 a=wKuvFiaSGQ0qltdbU6+NXLB8nM8=:19 a=Ol13hO9ccFRV9qXi2t6ftBPywas=:19 a=xqWC_Br6kY4A:10 a=6IFa9wvqVegA:10 a=5NMkWOWTNnoRhBXsKGgA:9 a=pILNOxqGKmIA:10 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8 a=O9eKFCKeAbaeM_hrNGkA:9 a=DL96v0RwKH6Gcv7r:21 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10 a=frz4AuCg-hUA:10 X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUwNjA1MDEzNSBTYWx0ZWRfX3VB+IFJk+gmT WXutbqBDHFRMM1ZTS+Iv+kbbphG9n8Ei6HZqE/qgTViVbsxnDBImYur/m6lZRqC1LqbM62y5g0f llcZjzgm70R9CVU4MyEmISrIm+KgthuyWm8sm3hXV/YNb5EoHS0yoTppYLOnIHMfCE/uHVgjtoq Xnb+T6uafHjSjkIyVE8QPRW1jIENb8so9CqZJxPsWpyg6fGZuwty5IIJi0HQ0ESr5SaI0QCJR8m ZZe+yw8Pny9o0mhtx8mr1nzAsy45yXmf0CiA3L5AAmmYuGPdsF9flYJish3qgJBLyGjNpPmIwk9 P/LJrEOqjQTpIYguOwTK9b2cW1s+KhJDxeedHPgX7cc7OZxfd5DOfBUpm95Vyvmfayk8JXI7k7i vyXkC/IPJjhGVNz2jRKYAdV6s9WegRMLjOv9AOzAWU9ov/Tjj4u8cYCf17MMSUQKnXUd+jli X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1099,Hydra:6.0.736,FMLib:17.12.80.40 definitions=2025-06-05_04,2025-06-05_01,2025-03-28_01 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 malwarescore=0 impostorscore=0 phishscore=0 priorityscore=1501 adultscore=0 mlxlogscore=999 clxscore=1011 spamscore=0 lowpriorityscore=0 bulkscore=0 mlxscore=0 suspectscore=0 classifier=spam authscore=0 authtc=n/a authcc= route=outbound adjust=0 reason=mlx scancount=1 engine=8.19.0-2505280000 definitions=main-2506050135 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FAPH3PPF1C905D6E6_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="windows-1252" Hello all, We=92re seeing intermittently very poor performance of a query, when occasi= onally a poor query plan is chosen. We=92re using Postgres 16.9. One suspicious factor when looking at the EXPLAIN ANALYZE output, is a very= wrong estimated number of rows to be returned from a text[] column queried= with =91&&=92. After playing around with a simple recreate (details below), it seems ANALY= ZE of the table is affected by the number of rows in the table. Statistic `= most_common_elems` is [null] when there=92s over 15,873 rows in the table w= hen analyzed. With fewer rows it=92s analyzed correctly. Is there any good explanation for this behaviour? Preferably we=92d like so= me way for proper `most_common_elems` statistics to be collected in our pro= duction database, in the hope that influences a good query plan to always b= e selected. In our production system there=92s ~150,000 rows in a table including a `te= xt[]` column, where each row has an array containing a single 19ish char st= ring, unique within the table. The full query joins against a couple more t= ables, and has a GIN index on the text[] column. If necessary, I can get in= to details of the real system, but hope the simple recreate will be suffici= ent to understand the problem: CREATE TABLE IF NOT EXISTS public.test( id SERIAL PRIMARY KEY, tags text[] ) INSERT INTO public.test (tags) SELECT ARRAY[TO_CHAR(n,'fm00000000')] FROM ( SELECT generate_series= (1,15_873) AS n ); ANALYZE public.test; SELECT * FROM pg_stat_user_tables WHERE relname =3D 'test'; EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT * FROM test WHERE tags && ARRAY['00000002'] Results ------- table with 15_000 rows has most_common_elems after ANALYZE (most_common_ele= m_freqs : 6.666667e-05) table with 15_872 rows has most_common_elems after ANALYZE (most_common_ele= m_freqs : 6.300403e-05) table with 15_873 rows has [null] most_common_elems after ANALYZE table with 100_000 rows has [null] most_common_elems after ANALYZE Query plans show an estimated 1 row is predicted when statistics has `most_= common_elems` available, or the hardcoded default 1/200 of the estimated ta= ble size when most_common_elems is null. Here 79 rows are estimated, when the table contained 15,873 rows and stats = weren=92t available. Query plan ----------- Seq Scan on public.test (cost=3D0.00..463.41 rows=3D79 width=3D37) (actual= time=3D9.934..17.190 rows=3D1 loops=3D1) Output: id, tags Filter: (test.tags && '{00000002}'::text[]) Rows Removed by Filter: 15872 Buffers: shared hit=3D268 Planning: Buffers: shared hit=3D75 Planning Time: 2.060 ms Execution Time: 17.205 ms Full version ------------ "PostgreSQL 16.9 (Debian 16.9-1.pgdg120+1) on aarch64-unknown-linux-gnu, co= mpiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit" Regards, Mark Frost IBM Unless otherwise stated above: IBM United Kingdom Limited Registered in England and Wales with number 741598 Registered office: Building C, IBM Hursley Office, Hursley Park Road, Winch= ester, Hampshire SO21 2JN --_000_PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FAPH3PPF1C905D6E6_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="windows-1252"

Hello all,

 

We=92re seeing inte= rmittently very poor performance of a query, when occasionally a poor query= plan is chosen. We=92re using Postgres 16.9.

One suspicious fact= or when looking at the EXPLAIN ANALYZE output, is a very wrong estimated nu= mber of rows to be returned from a text[] column queried with =91&&= =92.

 

After playing aroun= d with a simple recreate (details below), it seems ANALYZE of the table is = affected by the number of rows in the table. Statistic `most_common_elems` = is [null] when there=92s over 15,873 rows in the table when analyzed. With fewer rows it=92s analyzed correctly.

 

Is there any good e= xplanation for this behaviour? Preferably we=92d like some way for proper `= most_common_elems` statistics to be collected in our production database, i= n the hope that influences a good query plan to always be selected.

 

In our production s= ystem there=92s ~150,000 rows in a table including a `text[]` column, where= each row has an array containing a single 19ish char string, unique within= the table. The full query joins against a couple more tables, and has a GIN index on the text[] column. If necessa= ry, I can get into details of the real system, but hope the simple recreate= will be sufficient to understand the problem:

 

 

 

CREATE TABLE= IF NOT EXISTS public.test(

  =   id SERIAL PRIMARY KEY,

  =   tags text[]

)=

 <= /o:p>

INSERT INTO = public.test (tags)

  =       SELECT ARRAY[TO_CHAR(n,'fm00000000')] FROM (= SELECT generate_series(1,15_873) AS n );

 <= /o:p>

ANALYZE publ= ic.test;

 <= /o:p>

SELECT * FRO= M pg_stat_user_tables WHERE relname =3D 'test';

 <= /o:p>

EXPLAIN (ANA= LYZE,BUFFERS,VERBOSE)

  =       SELECT * FROM test WHERE tags && ARR= AY['00000002']

 <= /o:p>

 

 

 

Results

-------

table with 15_000 rows has most_common_elems aft= er ANALYZE (most_common_elem_freqs : 6.666667e-05)

table with 15_872 rows has most_common_elems aft= er ANALYZE (most_common_elem_freqs : 6.300403e-05)

table with 15_873 rows has [null] most_common_el= ems after ANALYZE 

table with 100_000 rows has [null] most_common_elems after ANALYZE =

 

 

 

Query plans show an= estimated 1 row is predicted when statistics has `most_common_e= lems` available, or the hardcoded default 1/200 of the estimated table size when most_common_elems is null.

Here 79 rows are estimated, when the table conta= ined 15,873 rows and stats weren=92t available.

 

Query plan

-----------

Seq Scan on public.= test  (cost=3D0.00..463.41 rows=3D79 width=3D37) (actual time=3D9.934.= .17.190 rows=3D1 loops=3D1)

  Output: id, = tags

  Filter: (tes= t.tags && '{00000002}'::text[])

 Rows Removed = by Filter: 15872

  Buffers: sha= red hit=3D268

Planning:

  Buffers: sha= red hit=3D75

Planning Time: 2.06= 0 ms

Execution Time: 17.= 205 ms

 

 

Full version

------------

"PostgreSQL 16= .9 (Debian 16.9-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc = (Debian 12.2.0-14) 12.2.0, 64-bit"

 

 

Regards,=

Mark Frost

IBM

Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road, Winch= ester, Hampshire SO21 2JN
--_000_PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FAPH3PPF1C905D6E6_--