On Tue, 2010-08-10 at 17:15 +0300, Heikki Linnakangas wrote:
> On 10/08/10 12:38, Boxuan Zhai wrote:
> > The difficult way is to generate the plans for children table in planner, as
> > the other commands like UPDATE and DELETE. However, because the structure of
> > MERGE plan is much more complex than the ordinary ModifyTable plans, this
> > job may not as simple as we expected. We need to adjust both the main plan
> > and the
> > merge actions to fit the children tables, which is not straight forward.
>
> This the approach you'll have to take. But actually, I'm surprised it
> doesn't happen to just work already. It should be opaque to the merge
> facility that the reference to the parent target table has inherited
> child tables - expanding the inherited table to scans of all the
> children should already be handled by the planner.
The support for UPDATE and SELECT of partitioned cases is very different
in the planner and was handled as separate implementation projects.
If we want a working MERGE in the next release, I suggest that we break
down this project in the same way and look at partitioned target tables
as a separate project.
One reason for suggesting this is that all MERGE statements have a
source table, whereas UPDATE and DELETEs did not always. The plan for a
simple UPDATE and DELETE against a partitioned table is simple, but the
plan (and performance) of a joined UPDATE or DELETE is not good:
postgres=# explain update p set col2 = x.col2 from x where x.col1 =
p.col1;
QUERY
PLAN
---------------------------------------------------------------------------
Update (cost=299.56..1961.18 rows=68694 width=20)
-> Merge Join (cost=299.56..653.73 rows=22898 width=20)
Merge Cond: (public.p.col1 = x.col1)
-> Sort (cost=149.78..155.13 rows=2140 width=10)
Sort Key: public.p.col1
-> Seq Scan on p (cost=0.00..31.40 rows=2140 width=10)
-> Sort (cost=149.78..155.13 rows=2140 width=14)
Sort Key: x.col1
-> Seq Scan on x (cost=0.00..31.40 rows=2140 width=14)
-> Merge Join (cost=299.56..653.73 rows=22898 width=20)
Merge Cond: (public.p.col1 = x.col1)
-> Sort (cost=149.78..155.13 rows=2140 width=10)
Sort Key: public.p.col1
-> Seq Scan on p1 p (cost=0.00..31.40 rows=2140
width=10)
-> Sort (cost=149.78..155.13 rows=2140 width=14)
Sort Key: x.col1
-> Seq Scan on x (cost=0.00..31.40 rows=2140 width=14)
-> Merge Join (cost=299.56..653.73 rows=22898 width=20)
Merge Cond: (public.p.col1 = x.col1)
-> Sort (cost=149.78..155.13 rows=2140 width=10)
Sort Key: public.p.col1
-> Seq Scan on p2 p (cost=0.00..31.40 rows=2140
width=10)
-> Sort (cost=149.78..155.13 rows=2140 width=14)
Sort Key: x.col1
-> Seq Scan on x (cost=0.00..31.40 rows=2140 width=14)
Those plans could use some love and attention before forcing Boxuan to
implement that.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
