Re: [HACKERS] Re: type coersion (was OR clause status)

> and the output plan is:
>         (
>            { EXPR
>            :typeOid 0
>            :opType op
>            :oper
>               { OPER
>               :opno 1137
>               :opid 0
>               :opresulttype 16
>               }
>
>            :args (
>               { VAR
>               :varno 1
>               :varattno -2
>               :vartype 26
>               :vartypmod -1
>               :varlevelsup 0
>               :varnoold 1
>               :varoattno -2
>               }
>
>               { CONST
>               :consttype 23
>               :constlen 4
>               :constisnull false
>               :constvalue  4 [  3  0  0  0 ]
>               :constbyval true
>               }
>            )
>            }
>         )
>
> Why does the Var have a type 26(int), and the constant a type of
> 23(oid)?  Where's the conversion function?

A conversion function is not necessary; the operator in the "opno" field
(1137) corresponds to the oid of the entry in pg_operator for "=" with
the correct arguments.

> Now, the existance of the function doesn't help either, but that is a
> different problem:
>         test=> explain select * from test where oid = oid(3);
>         NOTICE:  QUERY PLAN:
>         Seq Scan on test  (cost=1936.05 size=4916 width=8)
> Is it because there is a int4eqoid() function?

Yes. The function is called int4eqoid() (good guess :). And there is
also a function oideqint4().

There is a chance that this case would actually work if we just removed
those functions, since (in my test code only) I've made int4 and oid
"binary compatible" so the int4eq or oideq routines would be used
instead. The index support code might actually behave properly then.

> How to use an index on that?

So that is the problem for this case; there is actually a function which
matches the arguments exactly, so it is specified. *And* the same
function is (probably) not mentioned in the index configuration tables
pg_am*.

However, if we allowed the index support code to look for possible
matches on indices for the non-constant terms, and then look for the
best possible match for conversion routines on other terms, and then did
an "optimizer substitution", we might get better behavior. We would want
code to do the same kind of analysis for constant terms with function
calls and constant expressions too.

I'd be happy to work on the actual substitution code, but still don't
know what the planner does with indices. I'm starting to poke through it
like you are, but am farther behind.

I thought a good start would be to try addressing a case like this, and
allow the planner/indexer/optimizer to substitute "binary compatible"
indices. If we can succeed at that, then we would know what places need
to be touched to do more, like handling function calls and expressions
with constants.

                      - Tom