Thread: Cat the query be tuned further ?

Cat the query be tuned further ?

From

Adarsh Sharma

Date:

31 May 2013, 14:25:11

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

Re: Cat the query be tuned further ?

From

Victor Yegorov

Date:

31 May 2013, 14:51:11

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