2013/5/31 Adarsh Sharma <eddy.ada...@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
