Re: Performance on update from join
От | Jean-Luc Lachance |
---|---|
Тема | Re: Performance on update from join |
Дата | |
Msg-id | 3CD99D0B.DA4EA415@nsd.ca обсуждение исходный текст |
Ответ на | data loss due to improper handling of postmaster .... ("Rajesh Kumar Mallah." <mallah@trade-india.com>) |
Ответы |
Re: Performance on update from join
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-sql |
Tom, table the "c" is not implicated in your test query. Try: create table a (a1 int primary key, a2 int, a3 int, a4 int); create table b (b1 int, b2 int, b3 int, b4 int, primary key (b1, b2)); create table d (d1 int, d2 int, d3 int, d4 int, primary key (d1, d2)); explain update a set a4 = d.d2 from b,d where a.a2 = b.b1 and a.a3 = b.b2 andb.b3 = d.d1 and b.b4 = d.d2 and a.a4 >= d.d3 and a.a4 <= d.d4; Which is closer to what I have. +----------------------------------- | /\ A1 A2 A3 A4 B1 B2 B3 B4 D1 D2 D3 D4 | | | | | | | | +--------------+ | +---------+ | | | | | +--------------+ +---------+ Tom Lane wrote: > > Jean-Luc Lachance <jllachan@nsd.ca> writes: > > I was exploring ways to improve the time required to update a large > > table from the join of two others as in: > > UPDATE a FROM b, c; > > I found that whatever index I create, compound or not, PG insist on > > performing the cartesian product first. > > Surely not. > > test=# create table a (f1 int primary key, f2 int); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey' for table 'a' > CREATE > test=# create table b (f1 int primary key, f2 int); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey' for table 'b' > CREATE > test=# create table c (f1 int primary key, f2 int); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'c_pkey' for table 'c' > CREATE > > test=# explain update a set f2 = a.f2 + 1 from b,c where a.f1 = b.f1; > QUERY PLAN > > --------------------------------------------------------------------------------- > Nested Loop (cost=0.00..30121.50 rows=1000000 width=18) > -> Merge Join (cost=0.00..121.50 rows=1000 width=18) > Merge Cond: ("outer".f1 = "inner".f1) > -> Index Scan using a_pkey on a (cost=0.00..52.00 rows=1000 width=14) > -> Index Scan using b_pkey on b (cost=0.00..52.00 rows=1000 width=4) > -> Seq Scan on c (cost=0.00..20.00 rows=1000 width=0) > (6 rows) > > The target table doesn't have any special status in the planner's > consideration of possible join paths. So whatever problem you are > having, it's not that. How about providing a more complete description > of your tables and query? > > regards, tom lane
В списке pgsql-sql по дате отправления: