Re: psql is hanging

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(at)pinpointresearch(dot)com> wrote:

>
>
> On Fri, Nov 30, 2018 at 8:05 AM Chris Mair <chris(at)1006(dot)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
>
>
>
>