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 1q8nzy-0000Rm-8d for pgsql-performance@arkaria.postgresql.org; Mon, 12 Jun 2023 20:18:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q8nyz-0005Jk-1C for pgsql-performance@arkaria.postgresql.org; Mon, 12 Jun 2023 20:17:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q8nyy-0005Jb-IZ for pgsql-performance@lists.postgresql.org; Mon, 12 Jun 2023 20:17:44 +0000 Received: from 6.mo548.mail-out.ovh.net ([188.165.58.48]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q8nys-001qQK-7d for pgsql-performance@lists.postgresql.org; Mon, 12 Jun 2023 20:17:43 +0000 Received: from mxplan6.mail.ovh.net (unknown [10.108.20.240]) by mo548.mail-out.ovh.net (Postfix) with ESMTPS id F3EB62022E for ; Mon, 12 Jun 2023 20:17:34 +0000 (UTC) Received: from DAG6EX1.mxp6.local (172.16.2.51) by DAG6EX1.mxp6.local (172.16.2.51) 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 22:17:34 +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 22:17:34 +0200 From: benoit To: "pgsql-performance@lists.postgresql.org" Subject: 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+aJQ== Date: Mon, 12 Jun 2023 20:17:34 +0000 Message-ID: <7e0c07696f3f42189815f28c898c3133@hopsandfork.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: 2bd72c50-00a4-4f35-aae1-14afac36b728 x-ovh-tracer-id: 9033657906344316183 Content-Type: multipart/alternative; boundary="_000_7e0c07696f3f42189815f28c898c3133hopsandforkcom_" MIME-Version: 1.0 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: 0 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgedvhedrgeduhedgudegiecutefuodetggdotefrodftvfcurfhrohhfihhlvgemucfqggfjpdevjffgvefmvefgnecuuegrihhlohhuthemucehtddtnecunecujfgurhephffvufhtfffkihgtggesrgdtjhertddtudenucfhrhhomhepsggvnhhoihhtuceosggvnhhoihhtsehhohhpshgrnhgufhhorhhkrdgtohhmqeenucggtffrrghtthgvrhhnpeejtdehleefkedviefhffefueetgfetkeehtdekheekkefhhfekgfejkedthefhleenucffohhmrghinhepghhithhhuhgsrdgtohhmpdgurghlihgsohdrtghomhenucfkphepuddvjedrtddrtddruddpfeejrdehledrudegvddrieeknecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehinhgvthepuddvjedrtddrtddruddpmhgrihhlfhhrohhmpeeosggvnhhoihhtsehhohhpshgrnhgufhhorhhkrdgtohhmqedpnhgspghrtghpthhtohepuddprhgtphhtthhopehpghhsqhhlqdhpvghrfhhorhhmrghntggvsehlihhsthhsrdhpohhsthhgrhgvshhqlhdrohhrghdpoffvtefjohhsthepmhhoheegkedpmhhouggvpehsmhhtphhouhht List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_7e0c07696f3f42189815f28c898c3133hopsandforkcom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_7e0c07696f3f42189815f28c898c3133hopsandforkcom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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/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/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://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 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_7e0c07696f3f42189815f28c898c3133hopsandforkcom_--