Re: Join the master table with other table is very slow (partitioning)

[Ao Jianwang <aojw2008@xxxxxxxxx>]

Yes, the index name is par_est_2012_07_09_aid_index on the aid column. The plan is as follows. It seems looks better than the old one, since it choose the index scan. However, I don't think it's efficient, since it still append the result from child tables together, then join the small table (par_list). I expect each child table will join with the small table, then aggregate them together as the "UNION ALL" did. Any comments. Thanks.

explain

select * 

FROM  

par_est e

WHERE 

e.date BETWEEN '2012-07-12' and '2012-07-14'  

and e.aid = 310723177

and exists

 (

 select true

 from par_daily_list l

 where l.id = e.list_id and

  l.fid = 1 and

l.sid = 143441 and

  l.cid in (36, 39, 6000)

 )

                                                             QUERY PLAN                                                              

-------------------------------------------------------------------------------------------------------------------------------------

 Nested Loop  (cost=0.00..168.09 rows=1 width=16)

   ->  Index Scan using par_daily_list_sid_fid_cid_key on par_daily_list l  (cost=0.00..18.56 rows=2 width=4)

         Index Cond: ((sid = 143441) AND (fid = 1) AND (cid = ANY ('{36,39,6000}'::integer[])))

   ->  Append  (cost=0.00..74.71 rows=5 width=16)

         ->  Seq Scan on par_est e  (cost=0.00..0.00 rows=1 width=16)

               Filter: ((date >= '2012-07-08'::date) AND (date <= '2012-07-10'::date) AND (aid = 310723177) AND (l.id = list_id))

         ->  Seq Scan on par_est_2012_07 e  (cost=0.00..0.00 rows=1 width=16)

               Filter: ((date >= '2012-07-08'::date) AND (date <= '2012-07-10'::date) AND (aid = 310723177) AND (l.id = list_id))

         ->  Bitmap Heap Scan on par_est_2012_07_08 e  (cost=20.86..24.88 rows=1 width=16)

               Recheck Cond: ((aid = 310723177) AND (list_id = l.id))

               Filter: ((date >= '2012-07-08'::date) AND (date <= '2012-07-10'::date))

               ->  BitmapAnd  (cost=20.86..20.86 rows=1 width=0)

                     ->  Bitmap Index Scan on par_est_2012_07_08_aid_index  (cost=0.00..6.47 rows=138 width=0)

                           Index Cond: (aid = 310723177)

                     ->  Bitmap Index Scan on par_est_2012_07_08_le_index  (cost=0.00..14.11 rows=623 width=0)

                           Index Cond: (list_id = l.id)

         ->  Bitmap Heap Scan on par_est_2012_07_09 e  (cost=20.94..24.96 rows=1 width=16)

               Recheck Cond: ((aid = 310723177) AND (list_id = l.id))

               Filter: ((date >= '2012-07-08'::date) AND (date <= '2012-07-10'::date))

               ->  BitmapAnd  (cost=20.94..20.94 rows=1 width=0)

                     ->  Bitmap Index Scan on par_est_2012_07_09_aid_index  (cost=0.00..6.44 rows=134 width=0)

                           Index Cond: (aid = 310723177)

                     ->  Bitmap Index Scan on par_est_2012_07_09_le_index  (cost=0.00..14.22 rows=637 width=0)

                           Index Cond: (list_id = l.id)

         ->  Bitmap Heap Scan on par_est_2012_07_10 e  (cost=20.85..24.87 rows=1 width=16)

               Recheck Cond: ((aid = 310723177) AND (list_id = l.id))

               Filter: ((date >= '2012-07-08'::date) AND (date <= '2012-07-10'::date))

               ->  BitmapAnd  (cost=20.85..20.85 rows=1 width=0)

                     ->  Bitmap Index Scan on par_est_2012_07_10_aid_index  (cost=0.00..6.45 rows=135 width=0)

                           Index Cond: (aid = 310723177)

                     ->  Bitmap Index Scan on par_est_2012_07_10_le_index  (cost=0.00..14.11 rows=623 width=0)

                           Index Cond: (list_id = l.id)

(32 rows)