Re: EXPLAIN <query> command just hangs...

Thanks for replying Justin.

Yes, it may be an issue with the index, but I'd like to have some evidence towards that before dropping and recreating (It does not appear that bloat is a problem here or dead tuples either).

The EXPLAIN <query> statement is currently 'f' for waiting and 'active' via pg_stat_activity, so it is doing something. The ps command does not show me anything more verbose.

I had already checked the locks and blocking locks to see if there was an issue there and there was none. I can see there is AccessShareLock modes granted.

The reason I am very suspect of the timestamp column makeup is that if I remove that predicate from the EXPLAIN command and the actual query, both complete within seconds without issue. So I do know where the issue is (I just dont know what the issue is!).

Regards,

Ruan

________________________________
From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Sent: 02 November 2017 21:02
To: Rhhh Lin
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] EXPLAIN <query> command just hangs...

On Thu, Nov 02, 2017 at 08:51:23PM +0000, Rhhh Lin wrote:
> However, this query will run for days without completing. I suspect it has to do with the timestamp predicate and lack of using an appropriate index access path. This is what I need to verify/establish.

Perhaps the timestamp index is badly fragmented, and perhaps it would help to
reindex/cluster/pg_repack..

> So I try and perform a simple 'EXPLAIN <query>' in order to check what the planner has for the execution of this query.
> And after approx. six hours waiting, nothing has returned. It is still executing, but has not given me back my prompt (I can see the session is still active).My understanding is that the simple EXPLAIN version does not actually execute the query, so I do not understand why this is also performing poorly/hanging/stuck? Any ideas?

Is explain "wait"ing ? If you do "ps -fu postgres |grep EXPLAIN" does it say
"EXPLAIN waiting" ?

Or, if you "ps uww ThePID" does it show lots of CPU(or RAM) ?

If you do "SELECT * FROM pg_stat_activity WHERE pid=??" (from "ps" or from
SELECT pg_backend_pid() before starting "explain") does it show "active" state
or waiting ?

If it's waiting, you can see what it's waiting ON by looking at pg_locks..
Maybe like: SELECT c.query, * FROM pg_locks a JOIN pg_locks b USING(relation)
JOIN pg_stat_activity c ON b.pid=c.pid WHERE a.pid=?? (from ps)

Justin