Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q8pBi-0003Yo-U6 for pgsql-performance@arkaria.postgresql.org; Mon, 12 Jun 2023 21:34:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q8pBg-0006nQ-Ut for pgsql-performance@arkaria.postgresql.org; Mon, 12 Jun 2023 21:34:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q8pBg-0006nG-IX for pgsql-performance@lists.postgresql.org; Mon, 12 Jun 2023 21:34:56 +0000 Received: from smtpout2.mo529.mail-out.ovh.net ([79.137.123.220]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q8pBd-00201j-Cs for pgsql-performance@lists.postgresql.org; Mon, 12 Jun 2023 21:34:55 +0000 Received: from mxplan6.mail.ovh.net (unknown [10.108.16.246]) by mo529.mail-out.ovh.net (Postfix) with ESMTPS id 9D49820993; Mon, 12 Jun 2023 21:34:51 +0000 (UTC) Received: from DAG6EX1.mxp6.local (172.16.2.51) by DAG6EX2.mxp6.local (172.16.2.52) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2507.23; Mon, 12 Jun 2023 23:34:51 +0200 Received: from DAG6EX1.mxp6.local ([fe80::f5b3:cd71:6d1d:f0e0]) by DAG6EX1.mxp6.local ([fe80::f5b3:cd71:6d1d:f0e0%5]) with mapi id 15.01.2507.023; Mon, 12 Jun 2023 23:34:51 +0200 From: benoit To: Chris Hoover CC: "pgsql-performance@lists.postgresql.org" Subject: RE: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT Thread-Topic: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT Thread-Index: AQHZnWrs4DK20zdnFU+lNA3FhF+aJa+HhAsAgAArHKw= Date: Mon, 12 Jun 2023 21:34:51 +0000 Message-ID: <847f1886d2f94d619ebbd3d1394b74f1@hopsandfork.com> References: <7e0c07696f3f42189815f28c898c3133@hopsandfork.com>,<229CB377-96AD-493F-A454-A70F7915B69F@aweber.com> In-Reply-To: <229CB377-96AD-493F-A454-A70F7915B69F@aweber.com> Accept-Language: fr-FR, en-US Content-Language: fr-FR X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [37.59.142.68] x-ovh-tracer-guid: cf501169-0397-4051-b48d-62cea4316bb8 x-ovh-tracer-id: 10338857372727791002 Content-Type: multipart/alternative; boundary="_000_847f1886d2f94d619ebbd3d1394b74f1hopsandforkcom_" MIME-Version: 1.0 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: -100 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgedvhedrgeduiedgtdduucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuqfggjfdpvefjgfevmfevgfenuceurghilhhouhhtmecuhedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmnecujfgurhephffvvefuthffkfhfjghitgggsegrtdhjredttddunecuhfhrohhmpegsvghnohhithcuoegsvghnohhitheshhhophhsrghnughfohhrkhdrtghomheqnecuggftrfgrthhtvghrnhepvedvjedtuefhvddtgeeuheekuefgtdehheffueefueduueekuefgtdfggeejiefgnecuffhomhgrihhnpegurghlihgsohdrtghomhdpghhithhhuhgsrdgtohhmnecukfhppeduvdejrddtrddtrddupdefjedrheelrddugedvrdeikeenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepihhnvghtpeduvdejrddtrddtrddupdhmrghilhhfrhhomhepoegsvghnohhitheshhhophhsrghnughfohhrkhdrtghomheqpdhnsggprhgtphhtthhopedupdhrtghpthhtohepphhgshhqlhdqphgvrhhfohhrmhgrnhgtvgeslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhgpdgthhhrihhshhesrgifvggsvghrrdgtohhmpdfovfetjfhoshhtpehmohehvdelpdhmohguvgepshhmthhpohhuth List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_847f1886d2f94d619ebbd3d1394b74f1hopsandforkcom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This new index is used but still the read is 230mb. https://explain.dalibo.com/plan/b0f28a9e8a136afd ________________________________ De : Chris Hoover Envoy=E9 : lundi 12 juin 2023 22:55 =C0 : benoit Cc : pgsql-performance@lists.postgresql.org Objet : Re: Forced to use UNION ALL when having multiple ANY operators and = ORDER BY LIMIT I normally create my indexes to match the where clause of the query. While = technically, it should not matter, I find a lot of time, it does. I would create an index on (status, sender_reference, sent_at) and see if t= he improves your query performance. SELECT * FROM docs WHERE status IN ('draft', 'sent') AND sender_ref= erence IN ('Custom/1175', 'Client/362', 'Custom/280') ORDER BY sent_at DESC Thanks, Chris Hoover Senior DBA AWeber.com Cell: (803) 528-2269 Email: chrish@aweber.com On Jun 12, 2023, at 4:17 PM, benoit wrote: Hello I have a database with few 60gb tables. Tables rows are requested with mult= iple ANY or IN operators. I am not able to find an easy way to make DB able= to use indexes. I often hit the index, but see a a spike of 200mb of IO or= disk read. I am using version 13 but soon 14. I wrote a reproduction script on version 14 with plans included. https://gi= st.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d I also have plans on a snapshot of the DB with real data. - The current query that I try to improve : https://explain.dalibo.com/plan= /8b8f6e0he9feb551 - I added the DB schema + index in query view. As you can see I have many= indexes for testing purpose and try what the planner can do. - The optimized query when I have only one ANY and migrate to UNION ALL for= each parameter of the ANY operator https://explain.dalibo.com/plan/427gg05= 3d07328ga . Query is fast as I would like but it means generate some merge = to be able to get a fast result. - The new issue I have when I have a new ANY operator on the previous optim= ized query. Big IO/read https://explain.dalibo.com/plan/e7ha9g637b4eh946 It seems to me quite undoable to generate for every parameters a query that= will then merge. I have sometimes 3-4 ANY operators with up to 15 elements= in an array. Is there a misusage of my indexes? Is there a limitation when using ANY or IN operators and ordered LIMIT behi= nd? Thanks a lot --_000_847f1886d2f94d619ebbd3d1394b74f1hopsandforkcom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

This new index is used but still the read is 230mb.


https://explain.dalibo.com/plan/b0f28a9e8a136afd



De : Chris Hoover <chris= h@aweber.com>
Envoy=E9 : lundi 12 juin 2023 22:55
=C0 : benoit
Cc : pgsql-performance@lists.postgresql.org
Objet : Re: Forced to use UNION ALL when having multiple ANY operato= rs and ORDER BY LIMIT
 
I normally create my indexes to match the where clause of the query. W= hile technically, it should not matter, I find a lot of time, it does.

I would create an index on (status, sender_reference, sent_at) and see= if the improves your query performance.

SELECT * FROM docs WHERE status IN ('draft', 's= ent') AND sender_reference IN ('Custom/1175', 'C= lient/362', 'C= ustom/280') ORDER BY sent_at DESC
Thanks,


Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com



On Jun 12, 2023, at 4:17 PM, benoit <benoit@hopsandfork.com> wro= te:

Hello

I have a database with few 60gb tables. Tables rows are=  requested with multiple ANY or IN operators. I am not able to fi= nd an easy way to make DB able to use indexes. I often hit the index, but s= ee a a spike of 200mb of IO or disk read.

I am using version 13 but soon 14.

I wrote a reproduction script on version 14 with plans included. = https://gist.github.com/benoit= tgt/ab72dc4cfedea2a0c6a5ee809d16e04d

I also have plans on a snapshot of the DB with real data.
- The current query that I try to improve : = https://explain.dalibo.com/plan/8b8f6e0he9feb551
  - I added the DB schema + index in query view. As you can see I = have many indexes for testing purpose and try what the planner can do.
- The optimized query when I have only one ANY and migrate to UNION ALL for= each parameter of the ANY operator https:/= /explain.dalibo.com/plan/427gg053d07328ga . Query is fast as I would like but it means generate some merge to be able = to get a fast result.
- The new issue I have when I have a new ANY operator on the previous = optimized query. Big IO/read https://explai= n.dalibo.com/plan/e7ha9g637b4eh946

It seems to me quite undoable to generate for every parameters a query= that will then merge. I have sometimes 3-4 ANY operators with up to 15 ele= ments in an array.

Is there a misusage of my indexes?
Is there a limitation when using ANY or IN operators and ordered = LIMIT behind?

Thanks a lot

--_000_847f1886d2f94d619ebbd3d1394b74f1hopsandforkcom_--