Dear All,
I have two tables and i have to query my postgresql database.the table 1 has about 140 million records and table 2 has around 50 million records of the following.
the table 1 has the following structure:
tr_id bigint NOT NULL, # this is the primary key
query_id numeric(20,0), # indexed column
descrip_id numeric(20,0) # indexed column
and table 2 has the following structure
query_pk bigint # this is the primary key
query_id numeric(20,0) # indexed column
query_token numeric(20,0)
The sample db of table1 would be
1 25 96
2 28 97
3 27 98
4 26 99
The sample db of table2 would be
1 25 9554
2 25 9456
3 25 9785
4 25 9514
5 26 7412
6 26 7433
7 27 545
8 27 5789
9 27 1566
10 28 122 11 28 1456
I am preferring queries in which i would be able to query in blocks of tr_id. In range of 10,000 as this is my requirement.
I would like to get output in the following manner
25 {9554,9456,9785,9514}
26 {7412,7433}
27 {545,5789,1566}
28 {122,1456}
I tried in the following manner
select query_id, array_agg(query_token) from sch.table2 where query_id in(select query_id from sch.table1 where tr_id between 90001 and 100000) group by query_id
I am performing the following query which takes about 121346 ms and when some 4 such queries are fired it still takes longer time.can you please help me to optimise the same.
I have a machine which runs on windows 7 with i7 2nd gen proc with 8GB of RAM.
The following is my postgresql configurationshared_buffers = 1GB
effective_cache_size = 5000MB
work_mem = 2000MB
What should i do to optimize it.
Thanks
EDIT : it would be great if the results ordered according to the following format25 {9554,9456,9785,9514}
28 {122,1456}
27 {545,5789,1566}
26 {7412,7433}
ie according to the order of the queryid present in table1 ordered by tr_id.If this is computationally expensive may be in the client code i would try to optimize it.But i am not sure how efficient it would be.
--
Thanks
Gowthamie Balamurugan