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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux