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
