> > 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.
> > 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?
Before we go ripping into the parser, I think it would help to
understand the area of the optimizer or planner which decides whether or
not to use an index. Look, these first queries use an index:
regression=> explain select * from tenk1 where oid = 3000::oid;
Index Scan using tenk1_oid on tenk1 (cost=2.05 size=1 width=148)
regression=> explain select * from tenk1 where oid < 3000::oid;
Index Scan using tenk1_oid on tenk1 (cost=257.67 size=3334 width=148)
regression=> explain select * from tenk1 where oid > 3000::oid;
Index Scan using tenk1_oid on tenk1 (cost=257.67 size=3334 width=148)
But the next queries do not:
regression=> explain select * from tenk1 where oid != 3000::oid;
Seq Scan on tenk1 (cost=574.00 size=9999 width=148)
regression=> explain select * from tenk1 where oid = 3000;
Seq Scan on tenk1 (cost=574.00 size=1 width=148)
Afaik, the parser handles every one of these queries _exactly_ the same,
with slightly different outcomes due to the slight differences in
operators or types. But the parse tree will have the same kind of nodes,
with function calls used to evaluate the operators.
So how does the optimizer conclude that a node coming from the parser is
a possibility for an index scan? Let's find this out before masking the
behavior in the parser.
> In looking at the code, part of the problem is that you are creating a
> FuncCall node in coerce_type, so I have to make sure I convert normal
> funcs with constants to constants, and your type-conversion funcs too,
> which will not be picked up in the normal expression parsing because
> they were not there originally. (Assuming the function is cache-able.)
I think Vadim suggested that if we do something in the parser we create
a PARAM_EXEC node to do a one-time evaluation in the executor. Seems
preferable to doing a brute-force conversion via strings in the
parser...
- Tom