[Postgres-xc-general] Query Never Finishes When Trying to JOIN Multiple Tables

[Nick M. <nm...@gm...>]

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