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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
