Re: 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