[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
From: Benoit Tigeot <benoit () hopsandfork ! com>
Date: 2023-06-19 16:30:12
Message-ID: 8a143743-e850-693b-cc03-7f58eb33b646 () hopsandfork ! com
[Download RAW message or body]
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 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 <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
>
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<font face="monospace">No it is not. But do you think there is an
impact here?<br>
<br>
<br>
</font><br>
<div class="moz-cite-prefix">Le 18/06/2023 à 23:23, <a \
class="moz-txt-link-abbreviated" href="mailto:msalais@msym.fr">msalais@msym.fr</a> a \
écrit :<br> </div>
<blockquote type="cite"
cite="mid:005301d9a22b$28841ae0$798c50a0$@msym.fr">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<style>@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}span.pl-k
{mso-style-name:pl-k;}span.pl-s
{mso-style-name:pl-s;}span.pl-pds
{mso-style-name:pl-pds;}span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
mso-ligatures:none;}div.WordSection1
{page:WordSection1;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span \
style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US">Do you really need to do “select *”?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US">In other words, is it necessary to have all
columns in the result?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><i><span
style="font-size:9.0pt;color:#002060">Michel \
SALAIS<o:p></o:p></span></i></p> </div>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US">De :</span></b><span
lang="EN-US"> benoit <a class="moz-txt-link-rfc2396E" \
href="mailto:benoit@hopsandfork.com"><benoit@hopsandfork.com></a> <br> \
<b>Envoyé :</b> lundi 12 juin 2023 23:35<br>
<b>À :</b> Chris Hoover <a class="moz-txt-link-rfc2396E" \
href="mailto:chrish@aweber.com"><chrish@aweber.com></a><br>
<b>Cc :</b> <a class="moz-txt-link-abbreviated" \
href="mailto:pgsql-performance@lists.postgresql.org">pgsql-performance@lists.postgresql.org</a><br>
<b>Objet :</b> RE: Forced to use UNION ALL when having
multiple ANY operators and ORDER BY LIMIT<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div id="divtagdefaultwrapper">
<p><span style="font-size:12.0pt;color:black">This new index
is used but still the read is 230mb.<o:p></o:p></span></p>
<p><span style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
<p><span style="font-size:12.0pt;color:black"><a
href="https://explain.dalibo.com/plan/b0f28a9e8a136afd"
moz-do-not-send="true" \
class="moz-txt-link-freetext">https://explain.dalibo.com/plan/b0f28a9e8a136afd</a><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
<div>
<div class="MsoNormal" style="text-align:center"
align="center"><span style="font-size:12.0pt;color:black">
<hr width="98%" size="2" align="center"></span></div>
<div id="divRplyFwdMsg">
<p class="MsoNormal"><b><span style="color:black">De :</span></b><span
style="color:black"> Chris Hoover <<a
href="mailto:chrish@aweber.com"
moz-do-not-send="true" \
class="moz-txt-link-freetext">chrish@aweber.com</a>><br> <b>Envoyé :</b> lundi 12 \
juin 2023 22:55<br> <b>À :</b> benoit<br>
<b>Cc :</b> <a
href="mailto:pgsql-performance@lists.postgresql.org"
moz-do-not-send="true" \
class="moz-txt-link-freetext">pgsql-performance@lists.postgresql.org</a><br> \
<b>Objet :</b> Re: Forced to use UNION ALL when having multiple ANY operators and \
ORDER BY LIMIT</span><span
style="font-size:12.0pt;color:black"> <o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"> <o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">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. <o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">I would create
an index on (status, sender_reference, sent_at) and
see if the improves your query performance.<o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
<div>
<table class="MsoNormalTable"
style="border-collapse:collapse;border-spacing:0px;tab-size:8"
cellspacing="0" cellpadding="0" border="0">
<tbody>
<tr>
<td style="width:37.5pt;padding:0cm 7.5pt 0cm
7.5pt;min-width:50px"
id="file-issue_with_multiple_any-sql-L35"
width="50" valign="top" nowrap="nowrap"><br>
</td>
<td style="padding:0cm 7.5pt 0cm
7.5pt;overflow:visible"
id="file-issue_with_multiple_any-sql-LC35"
valign="top">
<p class="MsoNormal"
style="line-height:15.0pt"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920"><o:p> </o:p></span></p> \
<p class="MsoNormal" style="line-height:15.0pt"><span
class="pl-k"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">SELECT
* FROM docs WHERE</span></span><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">
status <span \
class="pl-k"><o:p></o:p></span></span></p> <p class="MsoNormal"
style="line-height:15.0pt"><span
class="pl-k"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">IN</span></span><span \
style="font-size:9.0pt;font-family:Consolas;color:#101920"> (<span \
class="pl-pds">'</span><span class="pl-s">draft</span><span
class="pl-pds">'</span>,<o:p></o:p></span></p>
<p class="MsoNormal"
style="line-height:15.0pt"><span
class="pl-pds"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">'</span></span><span \
class="pl-s"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">sent</span></span><span \
class="pl-pds"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">'</span></span><span \
style="font-size:9.0pt;font-family:Consolas;color:#101920">)<o:p></o:p></span></p> \
<p class="MsoNormal" style="line-height:15.0pt"><span
class="pl-k"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">AND</span></span><span \
style="font-size:9.0pt;font-family:Consolas;color:#101920">
sender_reference <span \
class="pl-k"><o:p></o:p></span></span></p> <p class="MsoNormal"
style="line-height:15.0pt"><span
class="pl-k"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">IN</span></span><span \
style="font-size:9.0pt;font-family:Consolas;color:#101920"> (<span \
class="pl-pds">'</span><span class="pl-s">Custom/1175</span><span
class="pl-pds">'</span>,<o:p></o:p></span></p>
<p class="MsoNormal"
style="line-height:15.0pt"><span
class="pl-pds"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">'</span></span><span \
class="pl-s"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">Client/362</span></span><span
class="pl-pds"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">'</span></span><span \
style="font-size:9.0pt;font-family:Consolas;color:#101920">,<o:p></o:p></span></p> \
<p class="MsoNormal" style="line-height:15.0pt"><span
class="pl-pds"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">'</span></span><span \
class="pl-s"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">Custom/280</span></span><span
class="pl-pds"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">'</span></span><span \
style="font-size:9.0pt;font-family:Consolas;color:#101920">)<o:p></o:p></span></p> \
<p class="MsoNormal" style="line-height:15.0pt"><span
class="pl-k"><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">ORDER \
BY</span></span><span
\
style="font-size:9.0pt;font-family:Consolas;color:#101920">
sent_at <span \
class="pl-k">DESC<o:p></o:p></span></span></p> <p class="MsoNormal"
style="text-align:right;line-height:15.0pt"
align="right"><span
style="font-size:10.0pt;font-family:"Times
New Roman",serif"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.5pt;color:#101920"><o:p> \
</o:p></span></p> <p class="MsoNormal"><span
style="font-size:10.5pt;color:#101920"><o:p> \
</o:p></span></p> </td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"> <o:p></o:p></span></p>
</div>
</div>
</div>
<div>
<div>
<p class="MsoNormal"><span
\
style="font-size:12.0pt;color:black">Thanks,<o:p></o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">Chris \
Hoover<o:p></o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">Senior \
DBA<o:p></o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
\
style="font-size:12.0pt;color:black">AWeber.com<o:p></o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">Cell: (803)
528-2269<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">Email: <a
href="mailto:chrish@aweber.com"
moz-do-not-send="true"
\
class="moz-txt-link-freetext">chrish@aweber.com</a><o:p></o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
</div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><br>
<br>
<o:p></o:p></span></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">On Jun 12,
2023, at 4:17 PM, benoit <<a
href="mailto:benoit@hopsandfork.com"
moz-do-not-send="true"
\
class="moz-txt-link-freetext">benoit@hopsandfork.com</a>> \
wrote:<o:p></o:p></span></p> </div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
<div>
<div id="divtagdefaultwrapper">
<div>
<p class="MsoNormal"><span
\
style="font-size:12.0pt;color:black">Hello<o:p></o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> \
</o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> \
</o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">I am
using version 13 but soon 14.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> \
</o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">I wrote
a reproduction script on version 14 with
plans included. <a
href="https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d"
moz-do-not-send="true"
\
class="moz-txt-link-freetext">https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> \
</o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">I also
have plans on a snapshot of the DB with real
data.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">- The
current query that I try to improve : <a
href="https://explain.dalibo.com/plan/8b8f6e0he9feb551"
moz-do-not-send="true"
\
class="moz-txt-link-freetext">https://explain.dalibo.com/plan/8b8f6e0he9feb551</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"> - 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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">- The
optimized query when I have only one ANY and
migrate to UNION ALL for each parameter of
the ANY operator <a
href="https://explain.dalibo.com/plan/427gg053d07328ga"
moz-do-not-send="true"
\
class="moz-txt-link-freetext">https://explain.dalibo.com/plan/427gg053d07328ga</a> . \
Query is fast as I would like but it means generate some merge to be able to get a \
fast result.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">- The
new issue I have when I have a new ANY
operator on the previous optimized query.
Big IO/read <a
href="https://explain.dalibo.com/plan/e7ha9g637b4eh946"
moz-do-not-send="true"
\
class="moz-txt-link-freetext">https://explain.dalibo.com/plan/e7ha9g637b4eh946</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> \
</o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> \
</o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">Is
there a misusage of my indexes?<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">Is
there a limitation when using ANY or IN
operators and ordered LIMIT behind?<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> \
</o:p></span></p> </div>
<div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black">Thanks
a lot<o:p></o:p></span></p>
</div>
</div>
</div>
</blockquote>
</div>
<p class="MsoNormal"><span
style="font-size:12.0pt;color:black"><o:p> </o:p></span></p>
</div>
</div>
</div>
</div>
</blockquote>
<br>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic