Problem with mergejoin performance (some bug?)

From: <azajac(at)ang(dot)com(dot)pl>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Problem with mergejoin performance (some bug?)
Date: 2010-09-12 17:34:00
Message-ID: 000001cb52a0$b02682f0$107388d0$@com.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message azajac 2010-09-13 05:49:45 Problem with mergejoin performance
Previous Message Stephen Frost 2010-09-11 02:03:37 Re: Slow SQL lookup due to every field being listed in SORT KEY