Usage of index in "ORDER BY" operations
От | Matthias Ackermann |
---|---|
Тема | Usage of index in "ORDER BY" operations |
Дата | |
Msg-id | 383FF97F.8F408F3B@webcraft.ch обсуждение исходный текст |
Ответы |
Re: [SQL] Usage of index in "ORDER BY" operations
|
Список | pgsql-sql |
I notice following behaviour: I have a table "adress" with 100'000 adresses with columns (last_name, first_name, adressline1, etc.) and an index last_name_idx on the column "last_name". The query "SELECT * FROM adress ORDER BY last_name LIMIT 20 OFFSET 0;" takes forever and "EXPLAIN" shows that the index on last_name is not being used. On the other hand "SELECT * FROM adress WHERE last_name > '' ORDER BY last_name LIMIT 20 OFFSET 0;" returns the result immediately and "EXPLAIN" shows that the index on last_name is being used. So it seems that inserting a WHERE-clause, even if it doesn't do anything at all (i.e. doesn't reduce the result-set), is necessary to force the DB to make use of the index. It even says in the FAQ under 4.9) "Indexes are not used for ORDER BY operations." So I was wondering: Am I doing something wrong here or is the lesson simply: "Include all attributes of an index in a where-clause if you want the indexes to be used"? Is there a better way to tell the DB to make use of the index? BTW: This seems to be true for indexes on multiple columns, i.e. if having an index on (last_name, first_name) the query had to be: SELECT * FROM adress WHERE last_name >'' AND first_name >'' ORDER BY last_name, first_name LIMIT 20 OFFSET 0; Omitting the where-clause again leads to a very slow query. I apologize if this has been discussed many times before ... Thanks for your help. Matt
В списке pgsql-sql по дате отправления: