To: Jean-Luc Lachance <jllachan@nsd.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance on update from join 
In-reply-to: <3CD94F3C.4B116582@nsd.ca> 
References: <20020507142203.4114.qmail@uwdvg001.cms.usa.net>
	<200205081145.37254.mallah@trade-india.com>
	<3CD94F3C.4B116582@nsd.ca>
Comments: In-reply-to Jean-Luc Lachance <jllachan@nsd.ca>
	message dated "Wed, 08 May 2002 12:15:56 -0400"
Date: Wed, 08 May 2002 17:00:30 -0400
Message-ID: <12514.1020891630@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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