Thread: bigint and indexes

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

"Bill Schneider" <bschneider@vecna.com> writes:
> This seems to be a well-known and documented issue.  Are there already plans
> to fix this in an upcoming release?

Yes.

> Is the problem here in the optimizer itself, or in the parser?

The problem is that the query is interpreted as "int8col int84eq int4const"
and int84eq is not one of the operators in the index's opclass.  The
planned fix is to get rid of int84eq (and most other cross-datatype
operators) so that the parser is forced to select plain int8eq and
up-convert the literal to int8.

See past discussions of numeric coercion rules in pgsql-hackers archives
if you want to know more about why this is a difficult issue.

            regards, tom lane