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