Skip site navigation (1) Skip section navigation (2)

Optimizing PostgreSQL Queries

From: Gowthamie Balamurugan <gowthamiebalamurugan(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Optimizing PostgreSQL Queries
Date: 2012-03-20 05:55:39
Message-ID: CABfLkq969yD4DeqQK4=_+=nrRhROZkWWvGY+sq5iFC8H16rarQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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 configuration
shared_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
format
25  {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

Responses

pgsql-novice by date

Next:From: Vincenzo MelandriDate: 2012-03-20 09:37:27
Subject: Hi all guys!
Previous:From: SharonDate: 2012-03-18 05:32:20
Subject: Warning console code page (437) differs from Window code page (1252)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group