Merge join and index scan strangeness

Hi!

I found something strange with merge join. Let there are two table
(http://www.sigaev.ru/misc/ex.sql.gz, 360Kb) t1 and t2, both without indexes.
Query is:
UPDATE    t1
SET    f1 = t1.f1 || t2.f1
FROM    t2
WHERE    t2.f1 = t1.f1 AND    t2.f2 = t1.f2 AND    t2.f3 = t1.f3 AND    t2.f4 = t1.f4
;

I forbid everything except merge join and index scan, so explain gives:
set enable_hashjoin=off;
set enable_nestloop=off;
set enable_seqscan=off;
set enable_bitmapscan=off;
 Merge Join  (cost=20000035240.26..20000388197.90 rows=14024070 width=82)   Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 =
t1.f2)AND (t2.f3 = t1.f3) AND
 
(t2.f4 = t1.f4))   ->  Sort  (cost=10000000040.69..10000000042.19 rows=600 width=59)         Sort Key: t2.f1, t2.f2,
t2.f3,t2.f4         ->  Seq Scan on t2  (cost=10000000000.00..10000000013.00 rows=600
 
width=59)   ->  Materialize  (cost=10000035199.57..10000038135.06 rows=234839 width=65)         ->  Sort
(cost=10000035199.57..10000035786.67rows=234839 width=65)               Sort Key: t1.f1, t1.f2, t1.f3, t1.f4
  ->  Seq Scan on t1  (cost=10000000000.00..10000005017.39
 
rows=234839 width=65)

All looks good at this point. Create index on suggested by merge join columns:
CREATE INDEX i1 ON t1 (f1, f2, f3, f4);
CREATE INDEX i2 ON t2 (f1, f2, f3, f4);

And explain: Merge Join  (cost=49897.68..402855.32 rows=14024070 width=82)   Merge Cond: ((t2.f4 = t1.f4) AND (t2.f1 =
t1.f1)AND (t2.f2 = t1.f2) AND
 
(t2.f3 = t1.f3))   ->  Sort  (cost=90.81..92.31 rows=600 width=59)         Sort Key: t2.f4, t2.f1, t2.f2, t2.f3
-> Index Scan using i2 on t2  (cost=0.00..63.13 rows=600 width=59)   ->  Materialize  (cost=49806.86..52742.35
rows=234839width=65)         ->  Sort  (cost=49806.86..50393.96 rows=234839 width=65)               Sort Key: t1.f4,
t1.f1,t1.f2, t1.f3               ->  Index Scan using i1 on t1  (cost=0.00..19624.68 rows=234839
 
width=65)

Merge join chooses another order of fields! It seems to me that index scan with
sort should be slower than pure index scan. Ok, add another indexes with
suggested column's order:

CREATE INDEX i11 ON t1 (f4, f1, f2, f3);
CREATE INDEX i21 ON t2 (f4, f1, f2, f3);

Explain: Merge Join  (cost=90.81..372665.64 rows=14024070 width=82)   Merge Cond: ((t1.f1 = t2.f1) AND (t1.f2 = t2.f2)
AND(t1.f3 = t2.f3) AND
 
(t1.f4 = t2.f4))   ->  Index Scan using i1 on t1  (cost=0.00..19624.68 rows=234839 width=65)   ->  Sort
(cost=90.81..92.31rows=600 width=59)         Sort Key: t2.f1, t2.f2, t2.f3, t2.f4         ->  Index Scan using i21 on
t2 (cost=0.00..63.13 rows=600 width=59)
 

Megre join uses index scan but for table t2 it uses wrong index! And again index 
scan + sort instead of index scan.

Am I miss something or misunderstand?


-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
  WWW: http://www.sigaev.ru/