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