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)
