[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: Performance tunning
From:       Jeff Janes <jeff.janes () gmail ! com>
Date:       2020-05-30 14:55:36
Message-ID: CAMkU=1xJTxf6Mj9Gc04ceTCrG2rjf9iMbjB4NOUtqC9OzZnsVg () mail ! gmail ! com
[Download RAW message or body]

On Sat, May 30, 2020 at 3:37 AM sugnathi hai <suganhai@yahoo.com> wrote:

> Hi ,
>
> Can you help to tune the below plan
>


It looks like your query (which you should show us) has something like

  ORDER BY modifieddate LIMIT 100

It thinks it can walk the index in order, then stop once it collects 100
qualifying rows.  But since almost all rows are removed by the join
conditions, it ends up walking a large chunk of the index before finding
100 of them which qualify.

You could try forcing it out of this plan by doing:

  ORDER BY modifieddate + interval '0 second' LIMIT 100

 Cheers,

Jeff

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Sat, May 30, 2020 at 3:37 AM sugnathi hai &lt;<a \
href="mailto:suganhai@yahoo.com">suganhai@yahoo.com</a>&gt; wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div \
style="font-family:&quot;Helvetica \
Neue&quot;,Helvetica,Arial,sans-serif;font-size:13px"><div dir="ltr"><div \
dir="ltr"><span style="color:rgb(0,0,0);font-family:Consolas;font-size:13.33px;white-space:pre-wrap">Hi \
,</span></div><div dir="ltr"><span \
style="color:rgb(0,0,0);font-family:Consolas;font-size:13.33px;white-space:pre-wrap"><br></span></div><div \
dir="ltr"><span style="color:rgb(0,0,0);font-family:Consolas;font-size:13.33px;white-space:pre-wrap"> \
Can you help to tune the below \
plan</span></div></div></div></div></blockquote><div><br></div><div><br></div><div>It \
looks like your query (which you should show us) has something \
like</div><div><br></div><div>   ORDER BY modifieddate LIMIT  \
100</div><div><br></div><div>It thinks it can walk the index in order, then stop once \
it collects 100 qualifying rows.   But since almost all rows are removed by the join \
conditions, it ends up walking a large chunk of the index before finding 100 of them \
which  qualify.</div><div><br></div><div>You could try forcing it out of this plan by \
doing:</div><div><br></div><div><div>   ORDER BY modifieddate  + interval &#39;0 \
second&#39; LIMIT  100</div><div></div></div><div><br></div><div>  \
Cheers,</div><div><br></div><div>Jeff</div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]