optimize_useless_left_join.txt

postgres=# CREATE TEMP TABLE foo(a int);
CREATE TABLE
Time: 301.708 ms
postgres=# CREATE TEMP TABLE bar(a int);
CREATE TABLE
Time: 2.748 ms
postgres=# SELECT foo.* FROM foo LEFT JOIN bar ON foo.a = bar.a;
 a 
---
(0 rows)

Time: 1.097 ms
postgres=# EXPLAIN SELECT foo.* FROM foo LEFT JOIN bar ON foo.a = bar.a;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Merge Left Join  (cost=337.49..781.49 rows=28800 width=4)
   Merge Cond: (foo.a = bar.a)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: foo.a
         ->  Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: bar.a
         ->  Seq Scan on bar  (cost=0.00..34.00 rows=2400 width=4)
(8 rows)

Time: 0.731 ms
postgres=# ANALYZE foo;
ANALYZE
Time: 0.703 ms
postgres=# ANALYZE bar;
ANALYZE
Time: 0.507 ms
postgres=# EXPLAIN SELECT foo.* FROM foo LEFT JOIN bar ON foo.a = bar.a;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Merge Left Join  (cost=337.49..781.49 rows=28800 width=4)
   Merge Cond: (foo.a = bar.a)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: foo.a
         ->  Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: bar.a
         ->  Seq Scan on bar  (cost=0.00..34.00 rows=2400 width=4)
(8 rows)

Time: 0.761 ms
postgres=# CREATE UNIQUE INDEX bar_idx ON bar (a);
CREATE INDEX
Time: 40.253 ms
postgres=# CREATE UNIQUE INDEX foo_idx ON foo (a);
CREATE INDEX
Time: 24.413 ms
postgres=# EXPLAIN SELECT foo.* FROM foo LEFT JOIN bar ON foo.a = bar.a;
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
(1 row)

Time: 0.941 ms