Lists: | pgsql-general |
---|
From: | nolan(at)celery(dot)tssi(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org (pgsql general list) |
Subject: | Optimizer failure on integer column? |
Date: | 2003-06-03 07:09:55 |
Message-ID: | 20030605014052.23168.qmail@celery.tssi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
Incidentally, why can't you define an alias on the primary table in
an update query? That would make the above a bit easier to write.
--
Mike Nolan
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | nolan(at)celery(dot)tssi(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org (pgsql general list) |
Subject: | Re: Optimizer failure on integer column? |
Date: | 2003-06-07 03:16:02 |
Message-ID: | 7931.1054955762@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
nolan(at)celery(dot)tssi(dot)com writes:
> Is there a documented problem with optimizing integer key fields
> in 7.3.3?
No. How about providing some more details, like EXPLAIN ANALYZE
output? The given query plan looks reasonable if the planner's
estimates are right ... but since you're complaining, I'd imagine
they are not. (Also, I assume you've done an ANALYZE lately?)
regards, tom lane
From: | "Jim C(dot) Nasby" <jim(at)nasby(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Optimizer failure on integer column? |
Date: | 2003-06-07 13:58:37 |
Message-ID: | 20030607135837.GI40542@flake.decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Do you have indexes on the tables?
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
From: | Jonathan Bartlett <johnnyb(at)eskimo(dot)com> |
---|---|
To: | pgsql general list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Optimizer failure on integer column? |
Date: | 2003-06-10 14:23:25 |
Message-ID: | Pine.GSU.4.44.0306100721040.2398-100000@eskimo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Is it an int8 column? If it is, you have to cast all raw numbers to
int8, or the optimizer won't catch that it can use the index.
For example, in perl
$dbh->prepare("select * from mytable where myinteger = ?::int8");
will be able to use the optimizer.
Jon
On Fri, 6 Jun 2003, Tom Lane wrote:
> nolan(at)celery(dot)tssi(dot)com writes:
> > Is there a documented problem with optimizing integer key fields
> > in 7.3.3?
>
> No. How about providing some more details, like EXPLAIN ANALYZE
> output? The given query plan looks reasonable if the planner's
> estimates are right ... but since you're complaining, I'd imagine
> they are not. (Also, I assume you've done an ANALYZE lately?)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>