On Tue, Sep 15, 2015 at 12:57 PM, Anastasia Lubennikova < a.lubennik...@postgrespro.ru> wrote:
> > Proposal Clarification. > I see that discussion become too complicated. So, I'd like to clarify > what we are talking about. > > We are discussing 2 different improvements of index. > The one is "partially unique index" and the other "index with included > columns". > Let's look at example. > > - We have a table tbl(f1, f2, f3, f4). > - We want to have an unique index on (f1,f2). > - We want to have an index on (f1, f2, f3) which allow us to use index for > complex "where" clauses. > Can someone write a query where F3 being ordered is a contribution? If F1 and F2 are unique, adding F3 to a where or order by clause doesn't seem to contribute anything. -- Already fully ordered by F1,F2 SELECT ... ORDER BY F1, F2, F3; -- F3 isn't in a known order without specifying F2 SELECT ... WHERE F1 = ? ORDER BY F1, F3; -- Index resolves to a single record; nothing to order SELECT ... WHERE F1 = ? AND F2 = ? ORDER BY F3; -- Without a where clause, the index isn't helpful unless F3 is the first column SELECT ... ORDER BY F3; What is it that I'm missing?
