Search Postgresql Archives

inner join elimination

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 

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)

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux