To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
From: Clailson <clailson.dba@gmail.com>
Subject: Optimization inner join
Message-ID: <e9a18035-bad1-1338-7447-41c678cf0800@gmail.com>
Date: Wed, 18 Jan 2017 16:56:49 -0300
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.6.0
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------A156BFC1D13149C1135522E5"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------A156BFC1D13149C1135522E5
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Hi,

Is there something in the roadmap to optimize the inner join?

I've this situation above. Table b has 400 rows with null in the column b.

explain analyze select * from a inner join b on (b.b = a.a);

"Merge Join  (cost=0.55..65.30 rows=599 width=16) (actual time=0.030..1.173 rows=599 loops=1)"
"  Merge Cond: (a.a = b.b)"
"  ->  Index Scan using a_pkey on a  (cost=0.28..35.27 rows=1000 width=8) (actual time=0.014..0.364 rows=1000 loops=1)"
"  ->  Index Scan using in01 on b  (cost=0.28..33.27 rows=1000 width=8) (actual time=0.012..0.249 rows=600 loops=1)"
"Total runtime: 1.248 ms"

My question is: Why the planner isn't removing the null rows during the 
scan of table b?

-- 
Clailson Soares Dinízio de Almeida


--------------A156BFC1D13149C1135522E5
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=windows-1252">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <font size="-1"><font face="Arial">Hi,<br>
        <br>
        Is there something in the roadmap to optimize the inner join?<br>
        <br>
        I've this situation above. Table b has 400 rows with null in the
        column b.<br>
      </font></font><br>
    explain analyze select * from a inner join b on (b.b = a.a);
    <pre>"Merge Join  (cost=0.55..65.30 rows=599 width=16) (actual time=0.030..1.173 rows=599 loops=1)" 
"  Merge Cond: (a.a = b.b)" 
"  -&gt;  Index Scan using a_pkey on a  (cost=0.28..35.27 rows=1000 width=8) (actual time=0.014..0.364 rows=1000 loops=1)" 
"  -&gt;  Index Scan using in01 on b  (cost=0.28..33.27 rows=1000 width=8) (actual time=0.012..0.249 rows=600 loops=1)" 
"Total runtime: 1.248 ms" 

<font size="-1"><font face="Arial">My question is: Why the planner isn't removing the null rows during the scan of table b?</font></font>
</pre>
    <pre class="moz-signature" cols="72">-- 
Clailson Soares Dinízio de Almeida
</pre>
  </body>
</html>

--------------A156BFC1D13149C1135522E5--