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

[Magorn <ma...@gm...>]

Hi,
Do you have indexes on your tables ?
Can you show the explain plan with your regular database ?
Regards,

On Wed, Aug 22, 2012 at 9:21 PM, Nick Maludy <nm...@gm...> wrote:

> 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
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>


-- 
Magorn