Hello,
I've also come across the bigint/index bug where an index on
table(bigint_col) is ignored for
explain select * from table where bigint_col = 83
results in a "Seq Scan" while
explain select * from table where bigint_col = '83'
and explain select * from table where bigint_col = 83::bigint
both result in an "Index Scan."
This seems to be a well-known and documented issue. Are there already plans
to fix this in an upcoming release?
Is the problem here in the optimizer itself, or in the parser? I'm not an
expert on postgresql-internals, so I don't understand why type resolution
for numeric literals happens at a different stage or uses a different
process from type resolution of quoted string literals. In other words, why
should quoted '83' be converted to a bigint or smallint or whatever to match
the other operand, while unquoted 83 is hard-converted to an int4 and never
gets promoted/reduced to match? The behavior would be more intuitive if
quoted '83' *didn't* match the index type and resulted in a Seq Scan just
like the unquoted number.
-- Bill