Hi all,
version = Postgres 9.3.10
I have a table with approx. 5 million rows. It is defined something like the below.
col: type:
timestamp bigint
measurement_id integer
value numeric(24,5)
minval numeric(24,5)
maxval numeric(24,5)
There are two BTree indexes in place on the PK ("timestamp", "measurement_id") and on ("measurement_id").
Problem:
I have an aggregate query along the lines of....
select count(*) as CNT, sum(value) as SUMVALUE, min(value) as MINVALUE, max(value) as MAXVALUE, timestamp
from my_table_name
where timestamp BETWEEN 1506676560000 AND 1508750220000
AND measurement_id IN (12345, 6789)
GROUP BY timestamp;
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.
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?
*Also, as a sidenote - can someone please expand on why one (I was not involved in the creation of this DB/schema definition) would choose to have the definition of the timestamp column as a bigint in this case?
Kind regards,
Ruan