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;