Postgres Pro
Downloads
Home   >   mailing lists

Re: Cat the query be tuned further ? - Mailing list pgsql-general

From Victor Yegorov
Subject Re: Cat the query be tuned further ?
Date
Msg-id CAGnEboj0kgv6ysE=q2TUgVr-10bpP6wZKEd-f-UyqKmuTX7yXw@mail.gmail.com
Whole thread Raw
In response to Cat the query be tuned further ?  (Adarsh Sharma <eddy.adarsh@gmail.com>)
List pgsql-general
Tree view
2013/5/31 Adarsh Sharma <eddy.adarsh@gmail.com>
explain analyze select sum(total_cost)as
cost,date_trunc('month',analytics_date)as monthDate from tableA
where inr_id in(select id from tableB where ct_id
='4028cb972f1ff337012f1ffa1fee0001') and analytics_date between '2013-01-14
00:00:00' and '2013-05-29 00:00:00' group by monthDate order by 2 desc

Your plan is here: http://explain.depesz.com/s/YzTZ

I would try:
1) CREATE INDEX i_tb_ct_id_id ON tableB (ct_id, id); VACUUM tableB;
    This will make it possible to use IndexOnly scan instead of BitmapScan.

2) Create new index on tableA (or change tableA_inr_dt perhaps):
    CREATE INDEX i_ta_inr_date_dtrunc ON tableA (inr_id,
        analytics_date, date_trunc('month',analytics_date), total_cost);
    
    Same here. I assume IndexOnly scan be used and also first 3 columns of the index
    will help with grouping.

Hope it helps.


--
Victor Y. Yegorov

pgsql-general by date:

Previous
From: Adarsh Sharma
Date:
Subject: Cat the query be tuned further ?
Next
From: Leif Jensen
Date:
Subject: ECPG SET CONNECTION