I ran the same report query regularly and most of the time the query completes within minutes. However, it sometimes gets stuck for hours in a nondeterministic way. When it got stuck, I repeated the same query with EXPLAIN ANALYZE, but the latter finished quickly.
Why is it so? How can get the execution plan of a running query?
When generating execution plans, PostgreSQL query planner relies heavily on table statistics. The statistics are updated by ANALYZE command, and the autovacuum process. In your case, it seems that most of the time the statistics are correct, but from time to time they become out of date which leads to an inefficient query plan. It can happen after bulk row inserts or updates.
In order to see the plan of a query after it has finished, use auto_explain module. For instance, the following commands configure the session to log plans of all queries that lasted more than an hour:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1h';
SET auto_explain.log_analyze = true;
The parameters can also be set in postgresql.conf. The module has to be loaded before the query starts.
If you want to obtain the plan of an already running query, and don't want to wait for its completion, you can use external script available at https://github.com/StarfishStorage/explain-running-query. The script is based on auto_explain module. It attaches gdb to PostgreSQL backend process, and prints the query plan.