From: | nolan(at)celery(dot)tssi(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org (pgsql general list) |
Subject: | Optimizer failure on update w/integer column |
Date: | 2003-06-15 22:05:32 |
Message-ID: | 20030615220532.24854.qmail@celery.tssi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Is there a documented problem with optimizing integer key fields
in 7.3.3?
I have two tables. One was extracted from the other and has
the following four columns.
mtranmemid varchar(8),
mtranseq integer,
mtransts char,
mtranmemtp varchar(2)
mtranseq is a unique index on both tables and contains no nulls.
When I try to do an update, it takes several hours. Here's what
explain says about the query:
explain update missing_ids
set mtransts = a.mtransts,
mtranmemtp = a.mtranmemtp
from memtran as a
where a.mtranmemid = missing_ids.mtranmemid
and a.mtranseq = missing_ids.mtranseq
QUERY PLAN
--------------------------------------------------------------------------------
Hash Join (cost=9231.64..58634.93 rows=1 width=48)
Hash Cond: ("outer".mtranseq = "inner".mtranseq)
Join Filter: ("outer".mtranmemid = "inner".mtranmemid)
-> Seq Scan on memtran a (cost=0.00..22282.57 rows=714157 width=26)
-> Hash (cost=6289.91..6289.91 rows=351891 width=22)
-> Seq Scan on missing_ids (cost=0.00..6289.91 rows=351891 width=22)
--
Mike Nolan
From | Date | Subject | |
---|---|---|---|
Next Message | nolan | 2003-06-15 22:08:50 | Why can't you define a table alias on an update? |
Previous Message | Tom Lane | 2003-06-15 21:52:01 | Re: full featured alter table? |