|
From: Nick M. <nm...@gm...> - 2012-08-22 19:22:02
|
All,
I am trying to run the following query which never finishes running:
SELECT sub_list.*
FROM sub_list
JOIN list AS listquery
ON listquery.sub_list_id = sub_list.sub_list_id
JOIN parent AS parentquery
ON parentquery.list_id = listquery.list_id
WHERE parentquery.time > 20000 AND
parentquery.time < 30000;
Please excuse my dumb table and column names this is just an example with
the same structure as a real query of mine.
CREATE TABLE parent (
name text,
time bigint,
list_id bigserial
);
CREATE TABLE list (
list_id bigint,
name text,
time bigint,
sub_list_id bigserial
);
CREATE TABLE sub_list (
sub_list_id bigint,
element_name text,
element_value numeric,
time bigint
);
I have tried rewriting the same query in the following ways, none of them
finish running:
SELECT sub_list.*
FROM sub_list,
(SELECT list.sub_list_id
FROM list,
(SELECT list_id
FROM parent
WHERE time > 20000 AND
time < 30000
) AS parentquery
WHERE list.list_id = parentquery.list_id
) AS listquery
WHERE sub_list.sub_list_id = listquery.sub_list_id;
SELECT sub_list.*
FROM sub_list
WHERE sub_list_id IN (SELECT sub_list_id
FROM list
WHERE list_id IN (SELECT list_id
FROM parent
WHERE time > 20000 AND
time < 30000);
By "not finishing running" i mean that i run them in psql and they hang
there, i notice that my datanode processes (postgres process name) are
pegged at 100% CPU usage, but iotop doesn't show any disk activity. When i
try to run EXPLAIN ANALYZE i get the same results and have to Ctrl-C out.
My parent table has 1 million rows, my list table has 10 million rows, and
my sub_list_table has 100 million rows.
I am able to run all of these queries just fine on regular Postgres with
the same amount of data in the tables.
Any help is greatly appreciated,
Nick
|