Thanks for replying Tom.
Ah, I misunderstood the purpose of what you were looking for from the "ps" command. Most definitely, the postgres process for this session is eating up CPU cycles. That fact is what drew us to investigate the session activity in the first place. The CPU (8 cores) on this host are currently being pegged because of a couple of sessions all experiencing issues with the same or similar queries against this very table.
The actual index on the table is a composite index on (timestamp, measurement_id), but it is also the primary key constraint, so I don't particularly want to go messing with it(Production system environment also).
My initial attempt to 'explain' the plan was to check if the query planner was utilising this composite index based on the predicates. If not, then I could reasonable add in an explicit index on "timestamp", but right now I cannot check and verify because it just hangs...
Regard,
Ruan
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: 02 November 2017 21:40
To: Rhhh Lin
Cc: Justin Pryzby; pgsql-general@postgresql.org
Subject: Re: [GENERAL] EXPLAIN <query> command just hangs...
Rhhh Lin <ruanlinehan@hotmail.com> writes:
> 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.
ps would confirm for sure whether it was eating CPU time, whereas I do not
particularly trust pg_stat_activity to tell you that.
> 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.
We've seen issues with the planner having trouble trying to determine the
extreme values of an indexed column, in cases where there are a lot of
uncommitted or recently-dead entries at the end of the index --- it does
a lot of work trying to verify the commit status of each entry in turn.
So I wonder if that might apply.
regards, tom lane