Re: making update/delete of inheritance trees scale better
От | Tom Lane |
---|---|
Тема | Re: making update/delete of inheritance trees scale better |
Дата | |
Msg-id | 3116227.1617159368@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: making update/delete of inheritance trees scale better (Amit Langote <amitlangote09@gmail.com>) |
Ответы |
Re: making update/delete of inheritance trees scale better
(Amit Langote <amitlangote09@gmail.com>)
|
Список | pgsql-hackers |
I noticed something else interesting. If you try an actually-useful UPDATE, ie one that has to do some computation in the target list, you can get a plan like this if it's a partitioned table: EXPLAIN (verbose, costs off) UPDATE parent SET f2 = f2 + 1; QUERY PLAN --------------------------------------------------------------------------- Update on public.parent Update on public.child1 parent_1 Update on public.child2 parent_2 Update on public.child3 parent_3 -> Append -> Seq Scan on public.child1 parent_1 Output: (parent_1.f2 + 1), parent_1.tableoid, parent_1.ctid -> Seq Scan on public.child2 parent_2 Output: (parent_2.f2 + 1), parent_2.tableoid, parent_2.ctid -> Seq Scan on public.child3 parent_3 Output: (parent_3.f2 + 1), parent_3.tableoid, parent_3.ctid But when using traditional inheritance, it looks more like: EXPLAIN (verbose, costs off) UPDATE parent SET f2 = f2 + 1; QUERY PLAN --------------------------------------------------------------------------- Update on public.parent Update on public.parent parent_1 Update on public.child1 parent_2 Update on public.child2 parent_3 Update on public.child3 parent_4 -> Result Output: (parent.f2 + 1), parent.tableoid, parent.ctid -> Append -> Seq Scan on public.parent parent_1 Output: parent_1.f2, parent_1.tableoid, parent_1.ctid -> Seq Scan on public.child1 parent_2 Output: parent_2.f2, parent_2.tableoid, parent_2.ctid -> Seq Scan on public.child2 parent_3 Output: parent_3.f2, parent_3.tableoid, parent_3.ctid -> Seq Scan on public.child3 parent_4 Output: parent_4.f2, parent_4.tableoid, parent_4.ctid That is, instead of shoving the "f2 + 1" computation down to the table scans, it gets done in a separate Result node, implying yet another extra node in the plan with resultant slowdown. The reason for this seems to be that apply_scanjoin_target_to_paths has special logic to push the target down to members of a partitioned table, but it doesn't do that for other sorts of appendrels. That isn't new with this patch, you can see the same behavior in SELECT. Given the distinct whiff of second-class citizenship that traditional inheritance has today, I'm not sure how excited people will be about fixing this. I've complained before that apply_scanjoin_target_to_paths is brute-force and needs to be rewritten, but I don't really want to undertake that task right now. regards, tom lane
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Amit LangoteДата:
Сообщение: Re: making update/delete of inheritance trees scale better
Следующее
От: Peter GeogheganДата:
Сообщение: Re: New IndexAM API controlling index vacuum strategies