[HACKERS] Merge join and index scan strangeness

Teodor Sigaev Fri, 19 Feb 2010 05:27:12 -0800

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.67 rows=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=234839 width=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.31 rows=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 indexscan + sort instead of index scan.


Am I miss something or misunderstand?


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Merge join and index scan strangeness

Reply via email to