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