Search Postgresql Archives

EXPLAIN <query> command just hangs...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux