From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jean-Luc Lachance <jllachan(at)nsd(dot)ca> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Performance on update from join |
Date: | 2002-05-08 21:00:30 |
Message-ID: | 12514.1020891630@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
Jean-Luc Lachance <jllachan(at)nsd(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Luc Lachance | 2002-05-08 21:47:55 | Re: Performance on update from join |
Previous Message | Laurette Cisneros | 2002-05-08 20:41:46 | pg_ctl |
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Luc Lachance | 2002-05-08 21:47:55 | Re: Performance on update from join |
Previous Message | Mike Diehl | 2002-05-08 20:58:59 | having trouble w/ having clause... |