Re: [PROPOSAL] Covering + unique indexes.

<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Tue, Sep 15, 2015 at 12:57 PM,
AnastasiaLubennikova <span dir="ltr"><<a href="mailto:a.lubennikova@postgrespro.ru"
target="_blank">a.lubennikova@postgrespro.ru</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="margin:0px0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br /><div bgcolor="#FFFFFF"
text="#000000">Proposal Clarification.<br /> I see that discussion become too <span>complicated. So, I'd like to
clarifywhat we are talking about.<br /><br /> We are discussing 2 different improvements of index.<br /> The one  is
"partiallyunique index" and the other  "index with included columns".<br /> Let's look at example.<br /><br /> - We
havea table tbl(f1, f2, f3, f4).<br /> - We want to have an unique index on (f1,f2).<br /> - We want to have an index
on(f1, f2, f3) which allow us to use index for complex "where" clauses.<br /></span><span
class=""></span></div></blockquote></div><br/><br /></div><div class="gmail_extra">Can someone write a query where F3
beingordered is a contribution?<br /><br /></div><div class="gmail_extra">If F1 and F2 are unique, adding F3 to a where
ororder by clause doesn't seem to contribute anything.<br /><br /> -- Already fully ordered by F1,F2<br /></div><div
class="gmail_extra">SELECT... ORDER BY F1, F2, F3;<br /><br /><br />-- F3 isn't in a known order without specifying
F2<br/></div><div class="gmail_extra">SELECT ... WHERE F1 = ? ORDER BY F1, F3;<br /></div><div class="gmail_extra"><br
/><br/>-- Index resolves to a single record; nothing to order<br /></div><div class="gmail_extra">SELECT ... WHERE F1 =
?AND F2 = ? ORDER BY F3;<br /><br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra">-- Without a
whereclause, the index isn't helpful unless F3 is the first column<br /></div><div class="gmail_extra">SELECT ... ORDER
BYF3; <br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Whatis it that I'm missing?<br /></div><div class="gmail_extra"><br /></div></div>