Re: [HACKERS] OR clause status - Mailing list pgsql-hackers

> > 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@candle.pha.pa.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)