Non-use of index ?
От | Peter Galbavy |
---|---|
Тема | Non-use of index ? |
Дата | |
Msg-id | 013001c1d583$04296ba0$2028a8c0@carpenter обсуждение исходный текст |
Ответы |
Re: Non-use of index ?
|
Список | pgsql-sql |
We have two seemingly identical SQL statements: SELECT user_name, status, count(status) AS total FROM messages WHERE client_id = '89' AND user_name in ('U66220', 'U66221') GROUP BY user_name, status; and SELECT user_name, status, count(status) AS total FROM messages WHERE (client_id = '89' AND user_name = 'U66221') OR (client_id = '89' AND user_name = 'U66220') GROUP BY user_name, status; The (relevant) index on that table is: CREATE INDEX messages_200203_ix2 ON messages_200203 (client_id, user_name); The first statement is *slow* by an order of magnitude in comparison to the second. Using EXPLAIN says that only the second statement is using the index... Now I can intellectually understand why the index is only used for the second statement, but shouldn't the optimiser be able to use the index to match (client_id, user_name) against the index through each combination and produce the second query by itself ? Using 'SET ENABLE_SEQSCAN TO OFF' makes no difference. As a side note, the original SQL from an Oracle database that is being ported to PostgreSQL did not suffer this 'problem'. Peter
В списке pgsql-sql по дате отправления: