Matching indexes on int8 columns?
От | Andrew Biagioni |
---|---|
Тема | Matching indexes on int8 columns? |
Дата | |
Msg-id | 3FA01013.1090105@e-greek.net обсуждение исходный текст |
Ответы |
Re: Matching indexes on int8 columns?
|
Список | pgsql-admin |
I'm running 7.3, and I recently observed something rather disconcerting, using EXPLAIN ANALYZE. If I have an index on an int8 column, and I run a query such as SELECT * FROM [tablename] WHERE [colname] = 12345 the index is NOT used, and a sequential scan of the 200K rows is done, taking ~ 17500 msec.; if I change the query to be: SELECT * FROM [tablename] WHERE [colname] = '12345' or SELECT * FROM [tablename] WHERE [colname] = 12345::int8 then the index is used and a btree index scan is performed taking ~24 msec. I found these workarounds in a Sept. 2001 thread, leading me to believe that it's nothing new; however, there is nothing in the docs. that I could find (including the Momjian book), that has any reference to this problem. My conclusion (assumption?) is that index-to-WHERE matching relies on some strict kind of type matching, without any attempt at type conversion between int4 and int8; but if a type conversion is forced (e.g., by quoting the number), then the conversion is done before the matching and all goes well. So finally, my question. Why is this behavior present? Is it actually a feature that I don't know enough to appreciate? Inquiring minds want to know... Thanks, Andrew Biagioni
В списке pgsql-admin по дате отправления: