bigint and indexes
bigint and indexes
От:
"Bill Schneider" <bschneider@vecna.com>
Дата:
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
Re: bigint and indexes
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Bill Schneider" 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
