Matching indexes on int8 columns

Поиск
Список
Период
Сортировка
От Andrew Biagioni
Тема Matching indexes on int8 columns
Дата
Msg-id OK85QLSNB0VRIDZFAUSNZWQO3UO21.3f9920a9@Laptop
обсуждение исходный текст
Список pgsql-admin
I'm running 7.3, and I recently found out something rather disconcerting:  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 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 id performed taking ~24 msec.

I found this workaround in a 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 is that index-to-WHERE matching relies on some strict kind of
type matching, without any attempt at type conversion between int4 and int8.

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 по дате отправления:

Предыдущее
От: James Cooper
Дата:
Сообщение: Migration Problems 7.2.3 -> 7.3.4
Следующее
От: "Per Münster"
Дата:
Сообщение: Access with a management tool