Re: Cat the query be tuned further ?

Поиск
Список
Период
Сортировка
От Victor Yegorov
Тема Re: Cat the query be tuned further ?
Дата
Msg-id CAGnEboj0kgv6ysE=q2TUgVr-10bpP6wZKEd-f-UyqKmuTX7yXw@mail.gmail.com
обсуждение исходный текст
Ответ на Cat the query be tuned further ?  (Adarsh Sharma <eddy.adarsh@gmail.com>)
Список pgsql-general
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Adarsh Sharma
Дата:
Сообщение: Cat the query be tuned further ?
Следующее
От: Leif Jensen
Дата:
Сообщение: ECPG SET CONNECTION