Re: Join the master table with other table is very slow (partitioning)
| От | Ao Jianwang |
|---|---|
| Тема | Re: Join the master table with other table is very slow (partitioning) |
| Дата | |
| Msg-id | CAAb+5fWUTpkZQ6vrL7m8Zu4XKBNC=uXp4SLUZVzgjQi0MBTvcA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Join the master table with other table is very slow (partitioning) (Ao Jianwang <aojw2008@gmail.com>) |
| Список | pgsql-performance |
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)
В списке pgsql-performance по дате отправления:
Предыдущее
От: Ao JianwangДата:
Сообщение: Re: Join the master table with other table is very slow (partitioning)
Следующее
От: Tom LaneДата:
Сообщение: Re: Join the master table with other table is very slow (partitioning)