Re: Use unique index for longer pathkeys.

On Mon, Jul 28, 2014 at 3:17 PM, Kyotaro HORIGUCHI <
horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> > Now drop the i_t1_pkey_1 and check the query plan again.
> >
> > drop index i_t1_pkey_1;
> > explain (costs off, analyze off) select * from t,t1 where t.a=t1.a
order by
> > t1.a,t1.b,t1.c,t1.d;
> > QUERY PLAN
> > ------------------------------------------------
> > Sort
> > Sort Key: t.a, t1.b, t1.c, t1.d
> > -> Merge Join
> > Merge Cond: (t.a = t1.a)
> > -> Index Scan using i_t_pkey on t
> > -> Index Scan using i_t1_pkey_2 on t1
> > (6 rows)
> >
> > Can't above plan eliminate Sort Key even after dropping index
> > (i_t1_pkey_1)?
>
> My patch doesn't so since there no longer a 'common primary
> pathkeys' in this query. Perhaps the query doesn't allow the sort
> eliminated. Since a is no more a pkey, t1 can have dulicate rows
> for the same a, so the joined relation also may have duplicte
> values in the column a.

I think irrespective of that we can trim t1.c & t1.d as we have
primary key (unique and non column) for t1.a, t1.b. Basically
even if we don't use the primary key index, we can still trim
the keys in such a case. IIUC, then Tom has mentioned the
same in his message related to this issue.

I am referring to below text:

"If we have "ORDER BY a, b, c" and (a,b) is the
primary key, then including c in the ORDER BY list is semantically
redundant, *whether or not we use an indexscan on the pkey index at all*."

http://www.postgresql.org/message-id/5212.1397599817@sss.pgh.pa.us

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com