From: <azajac@ang.com.pl>
To: <pgsql-performance@postgresql.org>
Subject: Problem with mergejoin performance (some bug?)
Date: Sun, 12 Sep 2010 19:34:00 +0200
Message-ID: <000001cb52a0$b02682f0$107388d0$@com.pl>
Thread-Index: ActSoK5NemfiZxkSQzqWVyaU3MQLCQ==
Content-Language: pl

I have a problem with some simple query:

select version();
 PostgreSQL 8.3.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.3.2
20081105 (Red Hat 4.3.2-7)
vacuum full bug_t1;
vacuum full bug_t2;
vacuum analyze bug_t1;
vacuum analyze bug_t2;
explain analyze SELECT ze.id ,rr.id FROM bug_t2 AS rr join bug_t1 AS ze ON
(ze.id=rr.id)  WHERE (ze.ids=94543);

Result is:

Merge Join  (cost=18.90..20.85 rows=1 width=8) (actual time=614.912..614.912
rows=0 loops=1)
   Merge Cond: (rr.id = ze.id)
   ->  Index Scan using bug_t2_i1 on bug_t2 rr  (cost=0.00..17893.49
rows=278417 width=4) (actual time=0.023..351.945 rows=278417 loops=1)
   ->  Sort  (cost=18.88..18.89 rows=4 width=4) (actual time=0.164..0.164
rows=1 loops=1)
         Sort Key: ze.id
         Sort Method:  quicksort  Memory: 17kB
         ->  Index Scan using bug_t1_i1 on bug_t1 ze  (cost=0.00..18.84
rows=4 width=4) (actual time=0.059..0.141 rows=4 loops=1)
               Index Cond: (ids = 94543)
 Total runtime: 615.003 ms

But after

SET enable_mergejoin=off;

result is:

Nested Loop  (cost=0.00..52.06 rows=1 width=8) (actual time=0.084..0.084
rows=0 loops=1)
   ->  Index Scan using bug_t1_i1 on bug_t1 ze  (cost=0.00..18.84 rows=4
width=4) (actual time=0.016..0.028 rows=4 loops=1)
         Index Cond: (ids = 94543)
   ->  Index Scan using bug_t2_i1 on bug_t2 rr  (cost=0.00..8.29 rows=1
width=4) (actual time=0.008..0.008 rows=0 loops=4)
         Index Cond: (rr.id = ze.id)
Total runtime: 0.154 ms


I think that problem is with estimation of total mergejoin time, why is it
so small (18.90..20.85) while estimates of subqueries (especially first) is
high (0..17893). Merging time should be high, because it needs to scan
almost all bug t2 table. Am I right?

Artur Zajac