Why don't use index on x when ORDER BY x, y?

Поиск
Список
Период
Сортировка
От Vlad Arkhipov
Тема Why don't use index on x when ORDER BY x, y?
Дата
Msg-id 5473103A.5040004@dc.baikal.ru
обсуждение исходный текст
Ответы Re: Why don't use index on x when ORDER BY x, y?
Re: Why don't use index on x when ORDER BY x, y?
Список pgsql-performance
Hello,

I wonder why Postgres does not use index in the query below? It is a
quite common use-case when you  want to sort records by an arbitrary set
of columns but do not want to create a lot of compound indexes for all
possible combinations of them. It seems that if, for instance, your
query's ORDER BY is x, y, z then any of these indexes could be used to
improve the performance: (x); (x, y); (x, y, z).

create temp table t as
select s as x, s % 10 as y, s % 100 as z
from generate_series(1, 1000000) s;

analyze t;
create index idx1 on t (y);

select *
from t
order by y desc, x
limit 10;


В списке pgsql-performance по дате отправления:

Предыдущее
От: Johann Spies
Дата:
Сообщение: Re: pgtune + configurations with 9.3
Следующее
От: Robert Klemme
Дата:
Сообщение: Re: Why don't use index on x when ORDER BY x, y?