inner join elimination

From: Arturo Guadagnin <tuirutuiru(at)gmail(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: inner join elimination
Date: 2018-05-29 08:30:19
Message-ID: 5b0d0f9a.1c69fb81.c74b7.a26c@mx.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christoph Moench-Tegeder 2018-05-29 09:20:46 Re: binaries for 11 beta compiled with --with-llvm?
Previous Message Thomas Kellerer 2018-05-29 06:47:10 Re: binaries for 11 beta compiled with --with-llvm?