From: Vlad Arkhipov <arhipov@dc.baikal.ru>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Why don't use index on x when ORDER BY x, y?
Date: Mon, 24 Nov 2014 19:02:18 +0800
Message-ID: <5473103A.5040004@dc.baikal.ru> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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;
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: arhipov@dc.baikal.ru
Subject: Re: Why don't use index on x when ORDER BY x, y?
In-Reply-To: <5473103A.5040004@dc.baikal.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox