From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance on update from join
Date: Wed, 08 May 2002 17:47:55 -0400
Message-ID: <3CD99D0B.DA4EA415@nsd.ca> (raw)
References: <20020507142203.4114.qmail@uwdvg001.cms.usa.net>
<200205081145.37254.mallah@trade-india.com>
<3CD94F3C.4B116582@nsd.ca>
<12514.1020891630@sss.pgh.pa.us>
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 and
b.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@nsd.ca, tgl@sss.pgh.pa.us
Subject: Re: Performance on update from join
In-Reply-To: <3CD99D0B.DA4EA415@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox