Message-ID: <5473103A.5040004@dc.baikal.ru>
Date: Mon, 24 Nov 2014 19:02:18 +0800
From: Vlad Arkhipov <arhipov@dc.baikal.ru>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Why don't use index on x when ORDER BY x, y?
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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;


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance