Query plan affected by lack of explicit cast?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Query plan affected by lack of explicit cast?
Date: 1998-07-24 19:00:11
Message-ID: 7315.901306811@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I made an index on the OID field of a table, but I find that the system
is still pretty picky about whether it will use the index or not.

tgl=> explain select * from history where oid = 34311;
NOTICE: QUERY PLAN:
Seq Scan on history (cost=25.66 size=1 width=100)
tgl=> explain update history set simstatus = '-' where oid = 34311;
NOTICE: QUERY PLAN:
Seq Scan on history (cost=25.66 size=1 width=94)

Oh dear, why isn't it using the index? By chance I tried this:

tgl=> explain select * from history where oid = 34311::oid;
NOTICE: QUERY PLAN:
Index Scan using history_oid_index on history (cost=21.92 size=179 width=100)
tgl=> explain update history set simstatus = '-' where oid = 34311::oid;
NOTICE: QUERY PLAN:
Index Scan using history_oid_index on marketorderhistory
(cost=2.05 size=1 width=94)

Much better. But why do I need to cast the constant to OID explicitly
to get a reasonable query plan? The system obviously knows that it has to
cast the thing to OID at some point... I'd have expected that to happen
before the query optimizer runs.

This is with recent development sources.

regards, tom lane

Browse pgsql-hackers by date

  From Date Subject
Next Message David Gould 1998-07-24 19:09:12 Re: [DOCS] Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]y
Previous Message James Olin Oden 1998-07-24 18:27:43 Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]