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 1tyQcf-006aDh-6K for pgsql-general@arkaria.postgresql.org; Sat, 29 Mar 2025 07:28:53 +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 1tyQcd-006ZSs-LA for pgsql-general@arkaria.postgresql.org; Sat, 29 Mar 2025 07:28:51 +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 1tyQcd-006ZSk-2t for pgsql-general@lists.postgresql.org; Sat, 29 Mar 2025 07:28:51 +0000 Received: from mail-dm6nam12olkn20811.outbound.protection.outlook.com ([2a01:111:f403:2c17::811] helo=NAM12-DM6-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tyQcb-001lBZ-0k for pgsql-general@postgresql.org; Sat, 29 Mar 2025 07:28:50 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=gOp7ene4FpAurFPjhftWik1hKFFZ4FQ04uW1IimLufd9qztENyRKDOJ3oM66DEwxlag3kvH5IcVWsq7PDyC4nYQFiXRFkbvTPCukM7mhf8NRUod0Gbcr6TJj9xa3pG0uRWgowzZFhr7QHsOTyGhrxZjg+Im2CdYSTRmHq1LfQv/I3ISyll+zVNMqH9Z0LjQmlnMI2LZJSRhvGHODq0iCRmyfZDmKfDNdsQ3y9hkbJceXXwzaSFdZ8kugffrT52uZ2NyN6X8+L9Tya4vPcU2l/04BGI2Pepua+rbVbPdQRfqvozwu94+WgjjABmLrMCJqfTcrq47SlufYMFcVtNuRAg== 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=0tIyIqdipA+0abOEkw/TCEZXXNyQFOodsygVuboF8y0=; b=tv6jaQ1dLjZfJ4QDzAzxrN3krm1//TeoeYM1FdtVKY5og7fXIbewxky80LIPEoLDsZxZLSSlTKq5HmCeMSrAXjG25AGhVuHDfIcdn718jx+PEnL4L2cV0qLZ1KLB0Aewru3RLIWQQlBIz3Tz7Shsn9fNeZC5Rvs6YocT5NTMsdIYCWPkH5lWWOqUOFxKpk+5u6ipB9ExSzCJqlndMi19Antgg9A32avnECAi8WZkjU6yNg7qQZyKj/+MtZaXM+NOypMIXy9nvB1J51Ftu6cWICW+oZ1tPRMUfEuZmKLba1x8/ALsHsgTlOaHVjI/titjeJY1uYcPu8KRPB0mkV7GKw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=0tIyIqdipA+0abOEkw/TCEZXXNyQFOodsygVuboF8y0=; b=ZggjrMGsxuAb/FCX4zNsxe3eR3A0WDG/eLclFLCNt8XjhPYihFJ6+IEayaQUKs4lWa0GzY6I/1UcZxuhigqk0OhYlRkHP8nJ7W4L9jqBEQd1zxpiuGB0777/O9/paVbATpcV4wM3GxCpgLdMOcRhBd7qIbSVDXY2/QTZAGIkh2ZUH6znmceTQ0FGPPOYZTEqElzzaIWkM5ySJuKLkrhP1wa6U6+40FI0C+cVnKWF99uruiMWZkd0dHhbci/H3apf9FE2s1vvFHvbVjSm0/989y8SEgPGmpwcGKsEoA9lJoB+SgQsMKu49jMV8iTv0xUYvWHWIQ+yIUCNK3BYOq8Xag== Received: from IA0PR19MB7217.namprd19.prod.outlook.com (2603:10b6:208:43f::21) by SN7PR19MB6565.namprd19.prod.outlook.com (2603:10b6:806:26f::6) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8534.44; Sat, 29 Mar 2025 07:28:46 +0000 Received: from IA0PR19MB7217.namprd19.prod.outlook.com ([fe80::c8d1:2003:98c6:348c]) by IA0PR19MB7217.namprd19.prod.outlook.com ([fe80::c8d1:2003:98c6:348c%3]) with mapi id 15.20.8534.043; Sat, 29 Mar 2025 07:28:46 +0000 From: Kevin Stephenson To: Moreno Andreo , PostgreSQL mailing lists Subject: Re: BTREE index: field ordering Thread-Topic: BTREE index: field ordering Thread-Index: AQHbn95aIWkDDnyW8kef8aqOfqyS/bOJt7Es Date: Sat, 29 Mar 2025 07:28:46 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: IA0PR19MB7217:EE_|SN7PR19MB6565:EE_ x-ms-office365-filtering-correlation-id: 95d7b11d-157f-4787-5ddd-08dd6e935752 x-ms-exchange-slblob-mailprops: ScCmN3RHayFXX0mVJp9wZC4LLIW1QoQ+hyHbBRe29dMCbycs3soS0soiFSTw3llVCcd71Sc4MZlWTWWkjTXqRupmy2vm0n2BsXG4LSlWCYlI9QYkSYSTSK02ztWNP3C/p4WUWpvW+OBFwOShILZCPv7uKWZxBnBF1ZqRk3/z2nY6LMjtK1tlzf/rCgyFBBbsswMWzy9B1YRGaTHEqbw4hcWLiKBji7GS7bO3i74r00zq61QFvx1lFv+BNBGQsH+R1mF80BkqJn+VrqetasKbriPEXp6q9fWClhl331/mvGhRV0uWxYw29Fv3Ic22XW2U8t8YC+XTS0eotwRwBMymSrWe5aA86SaOC//zeEQRejMxYPSu4p3jr4Y94KtDd0r739jSt8dAk5J58qYb0a4mT3zXjx2ToO1xvp+RfPE3JFI64x18HzHoyqOs9CizNXnlL/FelOLtzMDyugRfg0DRseZcraONRhNy5blt/22l9evOT3o8HQo1VaMiWn+AupJIxXHPp529jw7BTLCD1fPm6ySw0oh9iBu+i8gkvDJddb8rLUQyVGc+GtvOZfhfXwlyT6qWFGruZWobVIloK+WX/s5/EuBc8p9FVBw3dzjhnD07Av5fqtUbAKrElDYip3MCeGWqUSo0e5f3dLvkYDMV2frdjw4PZd1D0BCuu4p9E5/7LV+z2mJO+Bwos79kQbE1Gm3lh2J1sNICJis2rU6TBkPJiyz2xVzuiGO4L0q4ZVs= x-microsoft-antispam: BCL:0;ARA:14566002|8060799006|15030799003|19110799003|15080799006|7092599003|461199028|8062599003|440099028|3412199025|102099032; x-microsoft-antispam-message-info: =?us-ascii?Q?rzM/P0B7atWg/ZijthLIFy/2Gp6IpiopszZUW3plnR2grdQvph+ASdOu+BFU?= =?us-ascii?Q?ozFGpDX4x8Ys/5bx0gZMicqjZ30UI7tKYm6HCffslkMzQzabuD6EvsSo1HL7?= =?us-ascii?Q?eQVC2ELSNtwvucZTerwt8Y7y0qtCKxW4WIPLSCUxkC3Q0rv1WQnm9+u1Ji2L?= =?us-ascii?Q?mbatpyS70tHK6MP6TK56HJJJq8677X1z5vRbchGdAH/Q3olrQT8lI8k15meK?= =?us-ascii?Q?cUDdB047ATBTps3m8mE2MniLK713K4i12RFm/4p+PjBhRNnvGYlExZtcajHx?= =?us-ascii?Q?Y2siC4pUiLUI6mWH8qUzv4ydw4Yt1yBDtciLjfrBsjAFajlJXnJbvUngDkeC?= =?us-ascii?Q?rpqCTlAPWz9bTm4OmB0D0l8/KZPcgBOxBK73+bC0PY815Z5CDv6DbQh8evWF?= =?us-ascii?Q?bK/DFPb2WzsSY4D3oaTYhdGQXX2i0ukXhhLaHCF2oCKZLX6qfO8cJ8nwPIhU?= =?us-ascii?Q?/kj+Xw7O62BqAFi9bwppaKmgz9s/6KrwokK0fhLBfWxpP75Zu9Fqpvrt875x?= =?us-ascii?Q?uPjyfHHE6MAff662/07FjnmhGgQ7a0pEcbfOVjD0yh/WPnHZWXW6sHmXc3GJ?= =?us-ascii?Q?g3DY2p37Bhn8GmIAw59i0du7QijfF2GiJcCeHd2v1LSgiPINWFBkKNZrvH58?= =?us-ascii?Q?78Ik85hwKr6AAU3CtrmdH/vQZH+R1R0/CjeamQd/J7DzFzcVNPxWZ3Ku5oX+?= =?us-ascii?Q?/7HDztWeqUGF+9XgNvOiz12HJW/oTXjJyOLh849nQmLMpLOl/m2SI18L9Ygu?= =?us-ascii?Q?EoIrQtJY/51KTyXRqPN/ZXaX/XG6oh8uPHr2yyXevBsDf1s0IMrUj3L3h5f6?= =?us-ascii?Q?CRKPctsM896cli/vK5ytR4LZ4y1MMWVGNs8hmhflN1ur3tE8QC2IhaWeJLGI?= =?us-ascii?Q?nH3qiXtE77nAPL4AVy5vMuXg/eld8WMCvGK/6mBAoDPZI9Sw4F5eFqRNb+IL?= =?us-ascii?Q?V+2PYxlZgsRg1J7uNhF/OQeZ5dXWBg1o1Tzyc9z45Siep8P68gh1v7KqMCxA?= =?us-ascii?Q?1NiD7bzTDdjYzfJS0uFYxxiyjGud399R8RWWmU1Zv8oqkXpTfKszUqQqiNtK?= =?us-ascii?Q?n5VISU6ruehN/q04lvxt9MN9+/J3glcVL64VeuBFRunNe+PPFoj2LTkAYJNn?= =?us-ascii?Q?UqJ334n8z/f7xX+T6B1aEfXStGvwZSP4Bg=3D=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?dQ/91vJeyhcYh67oBJFZZTKU26W8Qba5V+64dhT6tlajkHUp6y3sC2WWYDH9?= =?us-ascii?Q?RsB5xXDpP/5Yjsz/cHO8BdJbBpT5KQ/6hFe/HaxXXg++wBQUxtQ6ZcGC+/r6?= =?us-ascii?Q?4jbRiPLN+tQMciuASlPzzomeGTpI8lY9O2gNks+ZSVkVRMMZcRZ1JRPwu+Ep?= =?us-ascii?Q?i97bmEPTfp5rW7h//OEupq7Yo9Aff1UE0TNwUeN2UozV8FeqifrqDD3Fk/e4?= =?us-ascii?Q?kUcNLizVqWWFwKnRNMt3axF3ImCVxhCw6vtA7wj+MF89M9GD8uMJYnGqFiI/?= =?us-ascii?Q?YH0C+VBzFMIF3qZmY1LImezYAbciDkqsXuLkD+VOO+QsV6wTDRlpY7IHshyH?= =?us-ascii?Q?I5DJRftxJkyn+U11GRewt3ZppUUN+qv9efoa7XFO7LlJ+S+AtYC0gumvQjEv?= =?us-ascii?Q?Lte5DRo7Cf9IKYlMyfNY1yNoQ6ChlH3Kyb3ODmybOnnDLPN27dY4jXC+eVuC?= =?us-ascii?Q?zLWEN0yrcx/pM1RIEVffi0Z9hKr6HheYyP3zxvQZkGiT6yFcWREuyIMzrdZd?= =?us-ascii?Q?6NP2oANfR9lhhnUJOkTEu2haPsbJqHX2BZAM48sqvADH9UkkXHImgUA46OGL?= =?us-ascii?Q?5KieoHKRWkDrQEXrWbLrlodfOF6gVloQQTsTfgStsNT+WcYguXBbwHBptAIc?= =?us-ascii?Q?BMGKvWc/zQ5+7MMgQkxR6Qxu5jSUf+yUE3kyekqJDuahcTErJm7EopmMjZ1h?= =?us-ascii?Q?vc6oLbPn/m8hZOKla18xaQHwotHKuvizi1I0mffFsY4X+5y2sLJCTuAoo3Hf?= =?us-ascii?Q?/U3SWdw0cYa28Zy3LdlLbnEB593hJTYhOKy6Q0hFbRzGF5cyTfVjPFWnP3uj?= =?us-ascii?Q?xbUid9fCOllOBJtZQ5kadfNaorWdNrsmeZ3vCbyjCcF0I08w5DkxOcTRFSvo?= =?us-ascii?Q?aQTPyLf6a6fe66SqK0kTYeq3NL7N8dG5ZLuIuCB6tvIDFZMQaNkXUqte/fgl?= =?us-ascii?Q?Y4Y7WoD5LV0TEPnWJYLkYy0KQSYjL63cDtwY1m02o53iBjkPVaaMGjY+GR+e?= =?us-ascii?Q?SU72gYTjqOf/5lAof1E8W3X2EGV8rEvAJOblkwYsEhidyULavUqQiTc0i3UJ?= =?us-ascii?Q?TMXhChq/ycwdWxk4sDo5i6WE+VER9odWCmfuX72xU74gWg9oDA74Bm4ewINg?= =?us-ascii?Q?FMVf37N7gdjsMuR5ytaplE5nPBDXUBHdyZlVVEzky03crmmtFCGJTpAeTpuW?= =?us-ascii?Q?A8kpVPQB4VCzqr6Dt+wfQOfRpCnMBbimeE5wfg=3D=3D?= Content-Type: multipart/alternative; boundary="_000_IA0PR19MB7217CBBA0C4461E6DFBBFABF8FA32IA0PR19MB7217namp_" MIME-Version: 1.0 X-OriginatorOrg: sct-15-20-7741-18-msonline-outlook-c907d.templateTenant X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: IA0PR19MB7217.namprd19.prod.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 95d7b11d-157f-4787-5ddd-08dd6e935752 X-MS-Exchange-CrossTenant-originalarrivaltime: 29 Mar 2025 07:28:46.0525 (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: SN7PR19MB6565 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_IA0PR19MB7217CBBA0C4461E6DFBBFABF8FA32IA0PR19MB7217namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Moreno, You have two lines in your WHERE clause commented out, the first line is a = duplicate check on flag =3D TRUE, and the other line involves several check= s on the "tipo" column. Will the final query or set of related queries actu= ally need to filter on the "tipo" column? You currently have "tipo" in seco= nd place in your candidate index. Thanks, Kevin ________________________________ From: Moreno Andreo Sent: Friday, March 28, 2025 5:38 AM To: PostgreSQL mailing lists Subject: BTREE index: field ordering Hi, Postgres 16.4 (planning to go on 17.4) I'm creating some indexes based on some slow query reported by logs. These queries involve a WHERE with more than 5 fields, that are matching by= =3D, <>, LIKE and IN() I read that equality fields must be first, then the others. Is it correct? Based on this query SELECT COUNT(id) AS total FROM nx.tbl1 WHERE (date_order >=3D '2025-03-21') AND (date_order <=3D '2025-03-29') AND (flag =3D TRUE) AND (( -- (flag =3D TRUE) -- AND (((tipo <> 'C') AND (tipo <> 'V') AND (tipo <> 'F')) O= R (tipo IS NULL) OR (tipo =3D '')) (((op <> 'C') OR (op IS NULL)) OR (tipo =3D 'F')) AND (s_state IN ('ENQ','WFR','BLQ','BLR','WFA','FRW','FRO','0= 000','0001')) AND (tiporic IS NOT NULL) AND (tiporic NOT LIKE '%cart%') ) OR ( (tiporic LIKE '%cart%') AND (S_state <> 'CON') AND (s_state <= > '0002') AND ((op <> 'C') OR (op IS NULL)) )) AND (priv IS NULL OR priv =3D false OR (priv =3D true and i= dpriv =3D 'TEST'))); Should the following index be correct? CREATE INDEX IF NOT EXISTS tbl1_count_idx on nx.tbl1 USING BTREE(flag, tipo= , op, priv, idpriv, date_order, s_state, tiporic); Would it be better to create a separate GIN/GIST index for the field matche= d with LIKE? Thanks in advance, Moreno --_000_IA0PR19MB7217CBBA0C4461E6DFBBFABF8FA32IA0PR19MB7217namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Moreno,

You have two lines in your WHERE clause commented out, the first line is a = duplicate check on flag =3D TRUE, and the other line involves several check= s on the "tipo" column. Will the final query or set of related qu= eries actually need to filter on the "tipo" column? You currently have "tipo" in second place in your candid= ate index.

Thanks,
Kevin
From: Moreno Andreo <mor= eno.andreo@evolu-s.it>
Sent: Friday, March 28, 2025 5:38 AM
To: PostgreSQL mailing lists <pgsql-general@postgresql.org> Subject: BTREE index: field ordering
 
Hi,
Postgres 16.4 (planning to go on 17.4)
I'm creating some indexes based on some slow query reported by logs.
These queries involve a WHERE with more than 5 fields, that are matching by= =3D, <>, LIKE and IN()
I read that equality fields must be first, then the others.
Is it correct?

Based on this query

SELECT COUNT(id) AS total
            &nb= sp;   FROM nx.tbl1
            &nb= sp;   WHERE
            (date_or= der >=3D '2025-03-21')
            AND (dat= e_order <=3D '2025-03-29')
            AND (fla= g =3D TRUE)
            AND (( <= br>             &nb= sp; -- (flag =3D TRUE)
            &nb= sp; -- AND (((tipo <> 'C') AND (tipo <> 'V') AND (tipo <>= 'F')) OR (tipo IS NULL) OR (tipo =3D ''))
            &nb= sp; (((op <> 'C') OR (op IS NULL)) OR (tipo =3D 'F'))
            &nb= sp; AND (s_state IN ('ENQ','WFR','BLQ','BLR','WFA','FRW','FRO','0000','0001= '))
            &nb= sp; AND (tiporic IS NOT NULL)
            &nb= sp; AND (tiporic NOT LIKE '%cart%')
            ) OR ( <= br>             &nb= sp; (tiporic LIKE '%cart%') AND (S_state <> 'CON') AND (s_state <&= gt; '0002') AND ((op <> 'C') OR (op IS NULL))
            )) = AND (priv IS NULL OR priv =3D false OR (priv =3D true and idpriv =3D 'TEST= ')));

Should the following index be correct?

CREATE INDEX IF NOT EXISTS tbl1_count_idx on nx.tbl1 USING BTREE(flag, tipo= , op, priv, idpriv, date_order, s_state, tiporic);

Would it be better to create a separate GIN/GIST index for the field matche= d with LIKE?

Thanks in advance,
Moreno
--_000_IA0PR19MB7217CBBA0C4461E6DFBBFABF8FA32IA0PR19MB7217namp_--