On Mon, Jul 28, 2014 at 3:17 PM, Kyotaro HORIGUCHI < horiguchi.kyot...@lab.ntt.co.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.1397599...@sss.pgh.pa.us With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
