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?  (Robert Klemme)
Re: Why don't use index on x when ORDER BY x, y?  (Tom Lane)
Список: 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 по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: Why don't use index on x when ORDER BY x, y?
От: Scott Marlowe
Дата:
Сообщение: Small performance regression in 9.2 has a big impact