2013/5/31 Adarsh Sharma <eddy.adarsh@xxxxxxxxx>
Your plan is here: http://explain.depesz.com/s/YzTZ
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
