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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
