JOIN REMOVAL - a short performance test

As i said yesterday, I'm back from FOSDEM where I have learned a LOT about our new version 9.0. For instance: join removal.

Explanation:

- Implement "join removal" for cases where the inner side of a left
  join is unique and is not referenced above the join.  In this case
  the inner side doesn't affect the query result and can be thrown
  away entirely.  Although perhaps nobody would ever write such a
  thing by hand, it's a reasonably common case in machine-generated
  SQL.

(copied from PostgreSQL Weekly News - September 20 2009)

Let's make a short performance test:

First I have created 2 tables:

test=# \d table_a
    Table "public.table_a"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
 value  | text    |
Indexes:
    "table_a_pkey" PRIMARY KEY, btree (id)

test=# \d table_b
    Table "public.table_b"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 value  | text    |

Let me populate this 2 tables, using another nice new feature: writeable CTE:

test=# with a as 
          (insert into table_a select s, md5(s::text) from generate_series(0,250000) s returning *), 
     b as                                                                                            
          (insert into table_b select id, md5(value) from a where substring(value,1,1) between '0' and '9') 
select true;                                                                                                
 bool                                                                                                       
------                                                                                                      
 t                                                                                                          
(1 row)                                                                                                     

Writeable CTE is a nice feature, isn't it? I love it ;-)

Okay. Now i have 2 populated tables. I have analysed this tables and I have created a VIEW:

test=*# create view view_table_ab as select a.id, a.value as value_a, b.value as value_b from table_a a left join table_b b on a.id=b.id;
CREATE VIEW                                                                                                                              

Okay, first test case without an index on table_b.id:

test=*# explain analyse select id, value_a from view_table_ab;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=7532.53..28639.73 rows=250001 width=37) (actual time=183.941..603.520 rows=250001 loops=1)            
   Hash Cond: (a.id = b.id)                                                                                                  
   ->  Seq Scan on table_a a  (cost=0.00..12917.01 rows=250001 width=37) (actual time=36.073..125.789 rows=250001 loops=1)   
   ->  Hash  (cost=5047.68..5047.68 rows=156068 width=4) (actual time=147.752..147.752 rows=156068 loops=1)                  
         ->  Seq Scan on table_b b  (cost=0.00..5047.68 rows=156068 width=4) (actual time=0.019..72.371 rows=156068 loops=1) 
 Total runtime: 661.780 ms                                                                                                   
(6 rows)                                                                                                                     

Okay, as expected an seq-scan on table b. The plan doesn't change even if I create a non-unique index on table_b(id):

test=*# create index idx_table_b_id on table_b(id);
CREATE INDEX
Time: 137,121 ms
test=*# explain analyse select id, value_a from view_table_ab;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=5346.53..18120.73 rows=250001 width=37) (actual time=141.921..548.674 rows=250001 loops=1)
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on table_a a  (cost=0.00..4584.01 rows=250001 width=37) (actual time=0.006..84.054 rows=250001 loops=1)
   ->  Hash  (cost=2861.68..2861.68 rows=156068 width=4) (actual time=141.856..141.856 rows=156068 loops=1)
         ->  Seq Scan on table_b b  (cost=0.00..2861.68 rows=156068 width=4) (actual time=0.005..66.048 rows=156068 loops=1)
 Total runtime: 607.385 ms
(6 rows)

Okay, let me drop this index and recreate it again, but with the unique-option, and re-run the select:

test=*# drop index idx_table_b_id;
DROP INDEX
Time: 16,038 ms
test=*# create unique index idx_table_b_id on table_b(id);
CREATE INDEX
Time: 122,602 ms
test=*# explain analyse select id, value_a from view_table_ab;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on table_a a  (cost=0.00..4584.01 rows=250001 width=37) (actual time=0.007..74.825 rows=250001 loops=1)
 Total runtime: 130.887 ms
(2 rows)

Time: 131,348 ms

Nice. Well, I think, this feature isn't a big thing. The new version 9.0 contains a LOT of really big things, this one is just a optimisation. But it works and it is useful. Thanks to Robert Haas and Tom Lane for this feature.


Andreas