Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group