Postgres Pro
Downloads
Home   >   mailing lists

Thread: Optimizer failure on integer column?

Optimizer failure on integer column?

From
nolan@celery.tssi.com
Date:
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

Re: Optimizer failure on integer column?

From
"Jim C. Nasby"
Date:
Do you have indexes on the tables?
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.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?"

Re: Optimizer failure on integer column?

From
Tom Lane
Date:
nolan@celery.tssi.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

Re: Optimizer failure on integer column?

From
Jonathan Bartlett
Date:
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@celery.tssi.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
>