Thread: inner join elimination

Hi

I was just wondering whether inner join elimination is working in postgres, so I put up a simple test case and compared it with an Oracle database (see output below).

It doesn't look like this feature is implemented in postgres, or am I missig something?

Are there any plans to implement it in the future?

-----------------------------------------

* Oracle 11.2

----------------------------------------

CREATE TABLE m (

   i INTEGER NOT NULL,

   c VARCHAR(10),

   CONSTRAINT m_pk PRIMARY KEY(i)

);

CREATE TABLE c (

   i INTEGER NOT NULL,

   created_tm DATE NOT NULL,

   CONSTRAINT c_pk PRIMARY KEY(i, created_tm),

   CONSTRAINT c_m_fk FOREIGN KEY(i) REFERENCES m

);

explain plan for   

select c.*

from m

  join c

    ON (m.i = c.i);   

select *

from table(dbms_xplan.display);   

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    22 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| C    |     1 |    22 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

----------------------------------------

* postgres 11-beta

----------------------------------------

CREATE TABLE m (

   i INTEGER NOT NULL,

   c VARCHAR(10),

   CONSTRAINT m_pk PRIMARY KEY(i)

);

CREATE TABLE c (

   i INTEGER NOT NULL,

   created_tm timestamp NOT NULL,

   CONSTRAINT c_pk PRIMARY KEY(i, created_tm),

   CONSTRAINT c_m_fk FOREIGN KEY(i) REFERENCES m

);

explain 

select c.*

from m

  join c

    ON (m.i = c.i);   

                           QUERY PLAN                           

-----------------------------------------------------------------

Hash Join  (cost=36.10..92.24 rows=2040 width=12)

   Hash Cond: (c.i = m.i)

   ->  Seq Scan on c  (cost=0.00..30.40 rows=2040 width=12)

   ->  Hash  (cost=21.60..21.60 rows=1160 width=4)

         ->  Seq Scan on m  (cost=0.00..21.60 rows=1160 width=4)