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 1qAzsI-0000Co-4l for pgsql-performance@arkaria.postgresql.org; Sun, 18 Jun 2023 21:23:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qAzsF-00076I-Iq for pgsql-performance@arkaria.postgresql.org; Sun, 18 Jun 2023 21:23:51 +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 1qAzsF-000769-0p for pgsql-performance@lists.postgresql.org; Sun, 18 Jun 2023 21:23:51 +0000 Received: from mout.kundenserver.de ([212.227.17.24]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qAzsB-002wFB-W7 for pgsql-performance@lists.postgresql.org; Sun, 18 Jun 2023 21:23:49 +0000 Received: from msim1 ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue109 [212.227.15.184]) with ESMTPSA (Nemesis) id 1Mnac7-1pjJxy0XsD-00jWYb; Sun, 18 Jun 2023 23:23:43 +0200 From: To: "'benoit'" , "'Chris Hoover'" Cc: References: <7e0c07696f3f42189815f28c898c3133@hopsandfork.com>,<229CB377-96AD-493F-A454-A70F7915B69F@aweber.com> <847f1886d2f94d619ebbd3d1394b74f1@hopsandfork.com> In-Reply-To: <847f1886d2f94d619ebbd3d1394b74f1@hopsandfork.com> Subject: RE: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT Date: Sun, 18 Jun 2023 23:23:42 +0200 Message-ID: <005301d9a22b$28841ae0$798c50a0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0054_01D9A23B.EC0F0DC0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQGgtpnXZ8ELNHVneniEOaEnuN/blgIZeJlhAdQ/742v43raAA== Content-Language: fr X-Provags-ID: V03:K1:+So5qMHZt71ZGWvvpPVLMgPj3NuGDQv25IRtCsb+lx09P6mnQhP xwCReAbdvNKKCua9tqaJ1sSjDgFqiFPBYoU9ae09meL+XW8miImbJqvxd6CJlvASU/josKl vTcti9N801ff6C5bZgyTF5o19KznHj5fb6e6BfiUVy/ym5+u9S0gU7ezerUQFb1tSN5cgrX IQTebKoXe2ct4fD81nT8w== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:eoZKK0bBHU8=;0NYyspP2aI+M+ugd+NDW1Add/Hi vn/8QCyjGniMgEH2Ofwj+vPoCpQCJh4fvgKT5H/jYYTJUdxDuydcm4I4lXhiYGRJlWoEzg4cm 6Foasl/Op2gRBClBzpeyHOpJrHOWuQDtqWm+xYTnSFa3oWoY/ZsT5AWY2UAjjpnYqTFLOfbeI JrviIbu4tBBJaiSY7uJHtHy6BMELRZ2e5hHJtwsAP3PPSKd4odbhQf3Rq5cnSblktJPgLto41 ilJOOW7IpTMe4c7TbMPqEARQ2e5zVjfJxBH4ZS2zA5ZaReVhJIxnZ7x7W9cIMhJ50V2nVJd/D I/lBl3Cb1E2rbuiszosAGk1aZo/JsOoiolZs0ZUC1wmGAU144Iw7Uz51vd5R1/758cm8Ckmqt I+qtwXk7Y0x2jS4BFuL5v1NAy9lmJnsKHW/i3e9UJnq61unM5FITyYLam3yHYpAlUo60isG1B pYmsFDeuYisRa6CRORlQ+mTF+VD611u6/HlHxvIugIR1m3lW7okvb5O5q8leZjK+abdDy5VmK LQYh4Pk+pW7qyrQB74+ZC6EchuIF49h53K1int+CMFcr+m1y2iCUVVJ28IyaDRrzMp7Gq/QtP llVfMexwGArOB9RpoGGYtunlGRjz2zUNB7OllIGIz/u4+E6PRDTvhwfTMoljHPsqKpmK7k8c5 8vmfiaqVMKXaPkkxkgVQyMXwQL80qf2MccxlsE3WwA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0054_01D9A23B.EC0F0DC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, Do you really need to do =93select *=94? In other words, is it necessary to have all columns in the result? =20 Michel SALAIS =20 De : benoit =20 Envoy=E9 : lundi 12 juin 2023 23:35 =C0 : Chris Hoover Cc : pgsql-performance@lists.postgresql.org Objet : RE: Forced to use UNION ALL when having multiple ANY operators = and ORDER BY LIMIT =20 This new index is used but still the read is 230mb. =20 https://explain.dalibo.com/plan/b0f28a9e8a136afd =20 _____ =20 De : Chris Hoover > Envoy=E9 : lundi 12 juin 2023 22:55 =C0 : benoit Cc : pgsql-performance@lists.postgresql.org =20 Objet : Re: Forced to use UNION ALL when having multiple ANY operators = and ORDER BY LIMIT=20 =20 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.=20 =20 I would create an index on (status, sender_reference, sent_at) and see = if the improves your query performance. =20 =09 =20 SELECT * FROM docs WHERE status=20 IN ('draft', 'sent') AND sender_reference=20 IN ('Custom/1175', 'Client/362', 'Custom/280') ORDER BY sent_at DESC =20 =20 =20 Thanks, =20 =20 Chris Hoover Senior DBA AWeber.com Cell: (803) 528-2269 Email: chrish@aweber.com =20 =20 =20 On Jun 12, 2023, at 4:17 PM, benoit > wrote: =20 Hello =20 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. =20 I am using version 13 but soon 14. =20 I wrote a reproduction script on version 14 with plans included. https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d =20 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 =20 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. =20 Is there a misusage of my indexes? Is there a limitation when using ANY or IN operators and ordered LIMIT behind? =20 Thanks a lot =20 ------=_NextPart_000_0054_01D9A23B.EC0F0DC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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=E9 : lundi 12 juin = 2023 23:35
=C0 : 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

 

<= div class=3DMsoNormal align=3Dcenter style=3D'text-align:center'>

De : Chris Hoover <chrish@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 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.

 

<= table class=3DMsoNormalTable border=3D0 cellspacing=3D0 cellpadding=3D0 = style=3D'border-collapse:collapse;border-spacing:0px;tab-size:8'>

 <= /o:p>

SELECT * = FROM docs WHERE status =

IN = ('draft',

'sent<= /span>')=

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:

 

<= div id=3Ddivtagdefaultwrapper>

Hello

<= div>

 

=

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

 

=
------=_NextPart_000_0054_01D9A23B.EC0F0DC0--