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

List:       postgresql-general
Subject:    Re: psql is hanging
From:       John Smith <localdevjs () gmail ! com>
Date:       2018-11-30 17:33:07
Message-ID: CAK6G+54+Em8NKj6yr0JpFJSyAvhOOh6JQa3D8-28rpM=4nsRag () mail ! gmail ! com
[Download RAW message or body]

Thanks Chris and Steve for the analyze suggestion; That will be my next
test!

On Fri, Nov 30, 2018 at 11:27 AM Steve Crawford <
scrawford@pinpointresearch.com> wrote:

>
>
> On Fri, Nov 30, 2018 at 8:05 AM Chris Mair <chris@1006.org> wrote:
>
>>
>> > We're kind of pulling out our hair here, any ideas?
>>
>> You might try issuing the command
>>
>>    analyze;
>>
>> right *before* the command that hangs.
>>
>>
> You might consider trying the "auto_explain" module (
> https://www.postgresql.org/docs/current/auto-explain.html ). This will
> let you "trap" the query plan used for the long-running query. Then compare
> that query plan with a manually run explain when it runs quickly to see if
> they differ. If they do, it suggests that bad statistics are a likely
> culprit and Chris' suggestion of running analyze will help.
>
> You only need to analyze those tables used in the query and, most likely,
> only tables that were substantially changed within a moderately short
> period prior to the start of the query.
>
> Autovacuum, which will handles analyze as well, typically defaults to
> checking for tables that need attention every minute so for processes that
> have a lot of steps it becomes "luck of the draw" whether or not a needed
> analyze is run after a substantial table change and before that table is
> used.
>
> We frequently put specific "analyze" statements in such scripts
> immediately following bulk-update statements.
>
> Cheers,
> Steve
>
>
>
>

[Attachment #3 (text/html)]

<div dir="ltr">Thanks Chris and Steve for the analyze suggestion; That will be my \
next test!<br></div><br><div class="gmail_quote"><div dir="ltr">On Fri, Nov 30, 2018 \
at 11:27 AM Steve Crawford &lt;<a \
href="mailto:scrawford@pinpointresearch.com">scrawford@pinpointresearch.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div \
dir="ltr"><br><br><div class="gmail_quote"><div dir="ltr">On Fri, Nov 30, 2018 at \
8:05 AM Chris Mair &lt;<a href="mailto:chris@1006.org" \
target="_blank">chris@1006.org</a>&gt; wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><br> &gt; We&#39;re kind of pulling out our hair \
here, any ideas?<br> <br>
You might try issuing the command<br>
<br>
     analyze;<br>
<br>
right *before* the command that hangs.<br>
<br></blockquote><div><br></div><div>You might consider trying the \
&quot;auto_explain&quot; module ( <a \
href="https://www.postgresql.org/docs/current/auto-explain.html" \
target="_blank">https://www.postgresql.org/docs/current/auto-explain.html</a> ). This \
will let you &quot;trap&quot; the query plan used for the long-running query. Then \
compare that query plan with a manually run explain when it runs quickly to see if \
they differ. If they do, it suggests that bad statistics are a likely culprit and \
Chris&#39; suggestion of running analyze will help.</div><div><br></div><div>You only \
need to analyze those tables used in the query and, most likely, only tables that \
were substantially changed within a moderately short period prior to the start of the \
query.</div><div><br></div><div>Autovacuum, which will handles analyze as well, \
typically defaults to checking for tables that need attention every minute so for \
processes that have a lot of steps it becomes &quot;luck of the draw&quot; whether or \
not a needed analyze is run after a substantial table change and before that table is \
used.</div><div><br></div><div>We frequently put specific &quot;analyze&quot; \
statements in such scripts immediately following bulk-update \
statements.</div><div><br></div><div>Cheers,</div><div>Steve</div><div><br></div><div><br></div><div><br></div></div></div></div>
 </blockquote></div>



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