Thread: Problem with mergejoin 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;
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

<azajac@ang.com.pl> writes:
> 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

> 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?

Actually, a mergejoin can stop short of processing all of either input,
if it exhausts the keys from the other input first; and the planner
knows that.  In this case it evidently thinks that the maximum key from
bug_t1 is much less than the maximum key from bug_t2, so that most of
the indexscan on bug_t2 won't have to be executed.  With only 4 rows
in bug_t1 it doesn't seem very likely that it would get this wrong.
What exactly are those join key values, and what are the min/max values
in bug_t2?

            regards, tom lane

[ Please keep the list cc'd ]

<azajac@ang.com.pl> writes:
>> What exactly are those join key values, and what are the min/max values
>> in bug_t2?

> min of Bug_t1.id = 42,
> max of Bug_t1.id = 393065,
> min of Bug_t2.id = 352448,
> max of Bug_t2.id = 388715,

> select count(id) from bug_t2
>     29
> select count(*) from bug_t2
>     278417
> And because there is only 29 not null records in bug_t2:

Oh, that's the real problem: thousands of nulls in bug_t2.
The planner is thinking those don't have to be scanned, but
the executor wasn't on the same page until very recently:
http://archives.postgresql.org/pgsql-committers/2010-05/msg00334.php

That patch will be in the next 8.3 update.

            regards, tom lane