Search Postgresql Archives

Trouble running nested select - Join query

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

 



Hi,

I have been working with Postgresxc 1.1 on a cluster, each cluster compute node having 48GB RAM.
I have instantiated the coordinator on one of the compute nodes and have 4 datanodes (2+2) running on two other compute nodes. 
I have been trying to run the  following query which is a select on two large, same size tables - miami_2d(head int, tail int) and miami_directednetwork(head int, tail int) (both of them have 105400516 rows).
miami_2d has been replicated on all datanodes and miami_directednetwork has been hash distributed on the head column across all 4 datanodes.
I have indexes on both the tables.
The query is:
copy (select * from (select s1.head as h1,s1.tail as t1 ,s2.tail as neighbor from miami_2d s1, miami_directednetwork s2 where s1.tail=s2.head and s2.tail not in (select tail from miami_2d where head=s1.head)) as O where O.h1!=O.neighbor) to '/tmp/tmp.txt'

Basically, the two tables are just a copy of each other having 'tail' as neighbor of 'head'. I am trying to find the distance-2 or 2 hop neighbor of the head column.  
For example:


miami_directednetwork                                                      
head | tail
400 | 336
400 | 209
400 | 487
336 | 400
336 | 209
336 | 500
487 | 400
487 | 391

miami_2d 
head | tail
400 | 336
400 | 209
400 | 487
336 | 400
336 | 209
336 | 500
487 | 400
487 | 391

Result:
head | tail | neighbor
400 | 336 | 500 
400 | 209 | 391

The issue is that every time I run the above query, it either gets killed by the system or it just keeps running for hours( I kill it after I see it running for more than a day).
I am using the following settings in the postgers.conf file:
For the datanodes:
effective_cache_size=10480MB
shared_buffers=3072MB
work_mem=10480MB
For the coordinator:
effective_cache_size=1048MB
shared_buffers=512MB
work_mem=1024MB

My question is: Is there anything wrong with the way I have framed the query or perhaps something else wrong? 
Any help is appreciated!

Regards,
Rushi

[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