Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id C9A976321B0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 13 Sep 2010 02:49:51 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 01060-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 13 Sep 2010 05:49:44 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from wentylator.ang.com.pl (wentylator.ang.com.pl [89.174.215.202])
	by mail.postgresql.org (Postfix) with ESMTP id 7329A6321CB
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Sep 2010 02:49:44 -0300 (ADT)
Received: from Artur7 (cfidsl [83.18.105.226])
	by wentylator.ang.com.pl (Postfix) with ESMTP id 7E4692DD286
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Sep 2010 07:49:42 +0200 (CEST)
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>
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: ActSoK5NemfiZxkSQzqWVyaU3MQLCQAZh2Cw
Content-Language: pl
x-cr-hashedpuzzle: A88z BJ8o B+xn DkOw DsWH EGBG Fzmp GCM1 HRsu Hb2s HzMS H5Ih
	IkbM J/Al KsvF Kv7f; 1;
	cABnAHMAcQBsAC0AcABlAHIAZgBvAHIAbQBhAG4AYwBlAEAAcABvAHMAdABnAHIAZQBzAHEAbAAuAG8AcgBnAA==;
	Sosha1_v1; 7; {E6708BA5-4628-4CDB-AAB0-9DC6C9AFE5D0};
	YQB6AGEAagBhAGMAQABhAG4AZwAuAGMAbwBtAC4AcABsAA==;
	Mon, 13 Sep 2010 05:49:43 GMT;
	UAByAG8AYgBsAGUAbQAgAHcAaQB0AGgAIABtAGUAcgBnAGUAagBvAGkAbgAgAHAAZQByAGYAbwByAG0AYQBuAGMAZQA=
x-cr-puzzleid: {E6708BA5-4628-4CDB-AAB0-9DC6C9AFE5D0}
X-wentylator-MailScanner-Information: Please contact the ISP for more
	information
X-wentylator-MailScanner: Found to be clean
X-wentylator-MailScanner-SpamCheck: not spam, SpamAssassin (not cached,
	score=0, required 6, autolearn=not spam)
X-wentylator-MailScanner-From: azajac@ang.com.pl
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201009/51
X-Sequence-Number: 40254


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