From: Phillip Couto <phillip@couto.in>
To: Clailson <clailson.dba@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Optimization inner join
Date: Thu, 19 Jan 2017 07:34:57 -0500
Message-ID: <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net> (raw)
In-Reply-To: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com>
References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
NULL is still a value that may be paired with a NULL in a.a
The only optimization I could see is if the a.a column has NOT NULL defined while b.b does not have NOT NULL defined.
Not sure if it is all that common. Curious what if you put b.b IS NOT NULL in the WHERE statement?
-----------------
Phillip Couto
> On Jan 19, 2017, at 05:08, Clailson <clailson.dba@gmail.com> wrote:
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: phillip@couto.in, clailson.dba@gmail.com
Subject: Re: Optimization inner join
In-Reply-To: <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox