Re: [HACKERS] OR clause status

> > Perhaps this is an artifact of the type-coercion issue (see "indexes
> > and floats" thread on pg-hackers). I find I have to write something
> > like
> > WHERE oid = 123456::oid
> > to get the system to use an index on OID. If I write
> > WHERE oid = 123456
> > it takes it, but does it by sequential scan :-(
> > I do not know if it's acted like that all along or it's a result
> > of Tom's type coercion fixes of a couple months ago.
>
> Hi Bruce. You are right, the optimizer is confusing :)
>
> I'm not sure if you were looking at this already, but I was thinking of
> finding the place where the optimizer decides whether an index can be
> used in a query, in particular when constants are involved. Seems like
> the overhead/operations involved should be identical whether the terms
> have the same type or not; in the cases above
> WHERE oid = 123456::oid
> would use oideq() and
> WHERE oid = 123456
> would use oidint4eq().
>
> Why would Postgres give up on using an index in the second case? In both
> cases there is one call to a function to evaluate the equality. Do the
> types need to match up for other reasons?

Because the PARSER converts :: casts to constants. It does not handle
functions-on-consts conversions.

In scan.l, :: is converted to TYPECAST, and then in gram.y:

| a_expr TYPECAST Typename
{
$$ = (Node *)$1;
/* AexprConst can be either A_Const or ParamNo */
if (nodeTag($1) == T_A_Const) {
((A_Const *)$1)->typename = $3;
} else if (nodeTag($1) == T_Param) {
((ParamNo *)$1)->typename = $3;
/* otherwise, try to transform to a function call */
} else {
FuncCall *n = makeNode(FuncCall);
n->funcname = $3->name;
n->args = lcons($1,NIL);
$$ = (Node *)n;
}
}

As you can see, if it is a constant as passed from scan.l, a constant is
created with the type of the cast, so it does become a constant.

Then in parse_expr.c, we have:

case T_A_Const:
{
A_Const *con = (A_Const *) expr;
Value *val = &con->val;

if (con->typename != NULL)
result = parser_typecast(val, con->typename, -1);
else
result = (Node *) make_const(val);
break;
}

And parser_typecast does, because the constant is an integer:

case T_Integer:
const_string = (char *) palloc(256);
string_palloced = true;
sprintf(const_string, "%ld", expr->val.ival);

which then does the conversion of the int to a string, and makes a
conversion back to the proper type:

cp = stringTypeString(tp, const_string, atttypmod);

stringTypeString does:

/* Given a type structure and a string, returns the internal form of
that string */
char *
stringTypeString(Type tp, char *string, int32 atttypmod)
{
Oid op;
Oid typelem;

op = ((TypeTupleForm) GETSTRUCT(tp))->typinput;
typelem = ((TypeTupleForm) GETSTRUCT(tp))->typelem; /* XXX - used for
* array_in */
return ((char *) fmgr(op, string, typelem, atttypmod));
}

and then makes a new constant:

adt = makeConst(typeTypeId(tp),
len,
(Datum) lcp,
false,
typeByVal(tp),
false, /* not a set */
true /* is cast */ );

The problem is that wrapping a function around the const is not going
through this code. I will see if I can add the proper calls to get it
working.

>
> I was thinking of adding the IS_BINARY_COMPATIBLE() macro as an
> optimization in the place where indices are being chosen, but then got
> confused as to why Postgres would care in the first place. Also, haven't
> found the area where these decisions are made.
>
> Any hints? Anyone else rummaged around that code?

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)