On Mon, Aug 4, 2014 at 1:22 PM, Kyotaro HORIGUCHI <
horiguchi.kyotaro@lab.ntt.co.jp> wrote:
>
> Hello,
>
> > 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.
>
> Although, yes, you're right, irrespective of the "common
> something", and even if the dropped index was i_t1_pkey_2, which
> is on t1(a, b), the result tuples are sorted as expected only by
> the pathkey (t.a = t1.a, t1.b). It is because both t and t1 are
> still unique so the joined tuples are also unique, and the unique
> key of the result tuples is the merged pkey (t.a, t1.a, t1.b),
> which can be transformed to (t.a, t1.b) using the equiality
> between t.a and t1.a. And considering the inner relation (t1) is
> already sorted by (a, b), the sort itself could be elimited from
> the plan.
I think if we could eliminate t1.c,t1.d considering indexes on
individual relations (may be by using technique I have mentioned
upthread or some other way), then the current code itself will
eliminate the ORDER BY clause. I have tried that by using a query
without having t1.c,t1.d in ORDER BY clause
explain (costs off, analyze off) select * from t,t1 where t.a=t1.a order by t1.a,t1.b;
QUERY PLAN
------------------------------------------
Merge Join
Merge Cond: (t1.a = t.a)
-> Index Scan using i_t1_pkey_2 on t1
-> Index Scan using i_t_pkey on t
(4 rows)
>
>
> > 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>
> Yes, my point at that time was mainly union (all) and partitioned
> tables so the optimization for joins was the next step for
> me.
Okay, I think you want to handle the elimination of ORDER BY clauses
at a much broader level which might handle most of simple cases as
well. However I think eliminating clauses as mentioned above is itself
a good optimization for many cases and more so if that can be done in
a much simpler way.
