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

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)