Re: Cat the query be tuned further ?

2013/5/31 Adarsh Sharma <eddy(dot)adarsh(at)gmail(dot)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