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 1qBHlp-0006oe-6L for pgsql-performance@arkaria.postgresql.org; Mon, 19 Jun 2023 16:30:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qBHlo-0002nd-2s for pgsql-performance@arkaria.postgresql.org; Mon, 19 Jun 2023 16:30:24 +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 1qBHln-0002n2-Gy for pgsql-performance@lists.postgresql.org; Mon, 19 Jun 2023 16:30:23 +0000 Received: from smtpout2.mo529.mail-out.ovh.net ([79.137.123.220]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qBHlj-0035SQ-W2 for pgsql-performance@lists.postgresql.org; Mon, 19 Jun 2023 16:30:22 +0000 Received: from mxplan6.mail.ovh.net (unknown [10.108.1.111]) by mo529.mail-out.ovh.net (Postfix) with ESMTPS id 9C37520534; Mon, 19 Jun 2023 16:30:15 +0000 (UTC) Received: from hopsandfork.com (37.59.142.108) 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.27; Mon, 19 Jun 2023 18:30:15 +0200 Authentication-Results: garm.ovh; auth=pass (GARM-108S002181233b8-e0f3-4167-9204-18f27356cd45, 08440929C5CD69269894F1E33E91425F62426AAF) smtp.auth=benoit@hopsandfork.com X-OVh-ClientIp: 156.146.63.161 Content-Type: multipart/alternative; boundary="------------sbKsQYAXsEhWa3TUwT7ZZjvU" Message-ID: <8a143743-e850-693b-cc03-7f58eb33b646@hopsandfork.com> Date: Mon, 19 Jun 2023 18:30:12 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:102.0) Gecko/20100101 Thunderbird/102.11.2 Subject: Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT Content-Language: en-GB To: , 'Chris Hoover' CC: References: <7e0c07696f3f42189815f28c898c3133@hopsandfork.com> <229CB377-96AD-493F-A454-A70F7915B69F@aweber.com> <847f1886d2f94d619ebbd3d1394b74f1@hopsandfork.com> <005301d9a22b$28841ae0$798c50a0$@msym.fr> From: Benoit Tigeot In-Reply-To: <005301d9a22b$28841ae0$798c50a0$@msym.fr> X-Originating-IP: [37.59.142.108] X-ClientProxiedBy: DAG2EX1.mxp6.local (172.16.2.11) To DAG6EX1.mxp6.local (172.16.2.51) X-Ovh-Tracer-GUID: e649919e-dfb9-4333-a6a2-16c6f9bff9c9 X-Ovh-Tracer-Id: 9409989948301135325 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: -100 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgedvhedrgeefvddguddttdcutefuodetggdotefrodftvfcurfhrohhfihhlvgemucfqggfjpdevjffgvefmvefgnecuuegrihhlohhuthemucehtddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenucfjughrpegtkfffgggfuffvvehfhfgjihesrgdtreertdefjeenucfhrhhomhepuegvnhhoihhtucfvihhgvghothcuoegsvghnohhitheshhhophhsrghnughfohhrkhdrtghomheqnecuggftrfgrthhtvghrnhepteehhfevgfeiudekgfdvtdehjefhkefgfeetffevgffhkeejfeeileelhfegleeunecuffhomhgrihhnpegurghlihgsohdrtghomhdpghhithhhuhgsrdgtohhmnecukfhppeduvdejrddtrddtrddupdefjedrheelrddugedvrddutdekpdduheeirddugeeirdeifedrudeiudenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepihhnvghtpeduvdejrddtrddtrddupdhmrghilhhfrhhomhepoegsvghnohhitheshhhophhsrghnughfohhrkhdrtghomheqpdhnsggprhgtphhtthhopedupdhrtghpthhtohepmhhsrghlrghishesmhhshihmrdhfrhdptghhrhhishhhsegrfigvsggvrhdrtghomhdpphhgshhqlhdqphgvrhhfohhrmhgrnhgtvgeslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhgpdfovfetjfhoshhtpehmohehvdelpdhmohguvgepshhmthhpohhuth List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------sbKsQYAXsEhWa3TUwT7ZZjvU Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: 8bit No it is not. But do you think there is an impact here? Le 18/06/2023 à 23:23, msalais@msym.fr a écrit : > > Hi, > > Do you really need to do “select *”? > > In other words, is it necessary to have all columns in the result? > > /Michel SALAIS/ > > *De :*benoit > *Envoyé :* lundi 12 juin 2023 23:35 > *À :* Chris Hoover > *Cc :* pgsql-performance@lists.postgresql.org > *Objet :* RE: Forced to use UNION ALL when having multiple ANY > operators and ORDER BY LIMIT > > This new index is used but still the read is 230mb. > > https://explain.dalibo.com/plan/b0f28a9e8a136afd > > ------------------------------------------------------------------------ > > *De :*Chris Hoover > *Envoyé :* lundi 12 juin 2023 22:55 > *À :* 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 the improves your query performance. > > > > > SELECT * FROM docs WHEREstatus > > IN('draft', > > 'sent') > > ANDsender_reference > > IN('Custom/1175', > > 'Client/362', > > 'Custom/280') > > ORDER BYsent_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 multiple 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://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 elements 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 > --------------sbKsQYAXsEhWa3TUwT7ZZjvU Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: 8bit No it is not. But do you think there is an impact here?



Le 18/06/2023 à 23:23, msalais@msym.fr a écrit :

Hi,

Do you really need to do “select *”?

In other words, is it necessary to have all columns in the result?

 

Michel SALAIS

 

De : benoit <benoit@hopsandfork.com>
Envoyé : lundi 12 juin 2023 23:35
À : Chris Hoover <chrish@aweber.com>
Cc : pgsql-performance@lists.postgresql.org
Objet : RE: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT

 

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

 

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

 

De : Chris Hoover <chrish@aweber.com>
Envoyé : lundi 12 juin 2023 22:55
À : 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 the improves your query performance.

 

 

SELECT * FROM docs WHERE status

IN ('draft',

'sent')

AND sender_reference

IN ('Custom/1175',

'Client/362',

'Custom/280')

ORDER BY sent_at DESC

 

 

 

Thanks,

 

 

Chris Hoover

Senior DBA

AWeber.com

Cell: (803) 528-2269

 

 



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

 

Hello

 

I have a database with few 60gb tables. Tables rows are requested with multiple 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://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 elements 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

 


--------------sbKsQYAXsEhWa3TUwT7ZZjvU--