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/
