From: azajac@ang.com.pl
To: pgsql-performance@postgresql.org
Subject: Problem with mergejoin performance
Date: Mon, 13 Sep 2010 07:49:45 +0200
Message-ID: <000001cb5307$78955480$69bffd80$@com.pl> (raw)
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;
show default_statistics_target;
1000
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: azajac@ang.com.pl
Subject: Re: Problem with mergejoin performance
In-Reply-To: <000001cb5307$78955480$69bffd80$@com.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox