From: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> |
---|---|
To: | Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Use unique index for longer pathkeys. |
Date: | 2014-07-29 12:50:19 |
Message-ID: | CAA4eK1JPWg=g3rO-B5Nci59doZ4G7wBecT3-XC5BG8X5Zc2grQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2014-07-29 13:00:26 | Re: Production block comparison facility |
Previous Message | MauMau | 2014-07-29 12:31:59 | Re: [RFC] Should smgrtruncate() avoid sending sinval message for temp relations |