Cat the query be tuned further ?

Поиск
Список
Период
Сортировка
От Adarsh Sharma
Тема Cat the query be tuned further ?
Дата
Msg-id CAGx-Qq+VVJdTM5kFbtYR12mL8UCeg3Le98488hw5rp=HEpdF-Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Cat the query be tuned further ?  (Victor Yegorov <vyegorov@gmail.com>)
Список pgsql-general
Hi all,

I am using EDB9.2  on CentOS6.3 final server. Facing the slowness of a query that is taking more than 20 sec to execute. Below are the details :

report_prod=# select pg_size_pretty(pg_total_relation_size('tableA'));
 pg_size_pretty
----------------
 5691 MB

report_prod=# select pg_size_pretty(pg_total_relation_size('tableB'));
 pg_size_pretty
----------------
 101 MB


Indexes:
    "tableA_pkey" PRIMARY KEY, btree (id)
    "tableA_inr_dt" btree (inr_id, analytics_date, is_bd, pr_mdl)


Indexes:
    "tableB_PK" PRIMARY KEY, btree (id)
    "tableB_inc_id" btree (inc_id)
    "tableB_ct_id" btree (ct_id)


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

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=22277.59..22278.40 rows=324 width=13) (actual
time=20247.160..20247.161 rows=5 loops=1)
   Sort Key: (date_trunc('month'::text, tableA.analytics_date))
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=22260.03..22264.08 rows=324 width=13) (actual
time=20247.105..20247.107 rows=5 loops=1)
         ->  Nested Loop  (cost=3.94..22223.47 rows=7313 width=13) (actual
time=147.932..20235.897 rows=5985 loops=1)
               ->  Bitmap Heap Scan on tableB  (cost=3.94..241.27 rows=84
width=33) (actual time=33.302..177.591 rows=84 loops=1)
                     Recheck Cond: ((ct_id)::text =
'4028cb972f1ff337012f1ffa1fee0001'::text)
                     ->  Bitmap Index Scan on tableB_ct_id  (cost=0.00..3.92
rows=84 width=0) (actual time=18.334..18.334 rows=95 loops=1)
                           Index Cond: ((ct_id)::text =
'4028cb972f1ff337012f1ffa1fee0001'::text)
               ->  Index Scan using tableA_site_dt on
tableA  (cost=0.00..260.61 rows=87 width=46) (actual
time=14.745..238.662 rows=71 loops=84)
                     Index Cond: (((inr_id)::text = (tableB.id)::text) AND
(analytics_date >= '2013-01-14 00:00:00'::timestamp without time zone) AND
(analytics_date <= '2013-05-29 00:00:00'::timestamp without time zone))
 Total runtime: 20248.266 ms

Shared_bufffers : 16 Gb

From the above i can see the nested loop is taking so much time. Is there any it can use hash join and it runs faster.

Please let me know if it can be tuned further.


Thansk

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: Understanding postgresql logs
Следующее
От: Victor Yegorov
Дата:
Сообщение: Re: Cat the query be tuned further ?