Re: Use unique index for longer pathkeys.

Hello,

> > 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)

Ya, the elimination seems to me so fascinate:)

> 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.

Yes, if can be done in "much" simpler way.. I guess that it
could be looking from opposite side, that is, equivalence
classes, anyway, I'll try it.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center