Dear hackers:
Is it reasonable or possible to have the optimizer filter out impossible
string/numeric matches because of length/overflow conditions?

For example: (on 7.4.1)

	select * from t where f = '1234567' -- f = char(6)
explain says index/seq scan, depending on presence of index, when
obviously there can be no matches.

also, for the typical case for indexed field f,
	select * from t where f = f' or false
generates an index scan

but 
	select * from t where f = f' or f = f''
generates a seq. scan with default settings.  If f'' is not in the
domain of f, the first case should apply.  Is this really as simple as
it seems?

Merlin




Wed, 10 Mar 2004 09:45:49 -0500
"Merlin Moncure" <merlin.moncure@rcsonline.com>
optimizing impossible matches
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Is this really as simple as it seems?

No.

The optimizer has no knowledge of specific operators except what it
finds in the system catalogs.  It has no way in general to determine
that a comparison involving nonconstant values must always fail.
Even if we could do it, I am dubious that it would be worth expending
the cycles on every query to determine whether the situation holds.
AFAICS those would be wasted cycles on the huge majority of queries.

BTW, your claim here is simply false:

> also, for the typical case for indexed field f,
> 	select * from t where f = f' or false
> generates an index scan
> but 
> 	select * from t where f = f' or f = f''
> generates a seq. scan with default settings.

Depending on the statistics of the column f, the optimizer might think
that a seqscan is better than a double indexscan, but it's not some kind
of hardwired behavior.  I don't even think it's "typical" behavior.

			regards, tom lane

Wed, 10 Mar 2004 10:21:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: optimizing impossible matches 