Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cUCOS-0001Fb-HT for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 13:04:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cUCOS-0004iJ-4U for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 13:04:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cUCOR-0004iC-DE for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 13:04:43 +0000 Received: from mail-ua0-x235.google.com ([2607:f8b0:400c:c08::235]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cUCON-0003RY-Iy for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 13:04:42 +0000 Received: by mail-ua0-x235.google.com with SMTP id i68so32651344uad.0 for ; Thu, 19 Jan 2017 05:04:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to; bh=LCMYNfRoPjnLFqVM6T+H24UaO9c7oj2RTAEpWJst5aQ=; b=SQ3B8FlLwIepgJdAab9mvk43rcCAGlS2/cVBrj7JWy4oG48K3pOfmEepRfaBVsq81a emLMiQZGVp7Uz96eZCP62vTA5untmaGJ5JCHYzYiFBtcykNWlkownN4IIusU9whbTYi4 6F/su0W3ZX70EUYDcMHEQnjT1jYq9Ddvl7UfMtIa9NjVHA6E+n/C0YA5dlSsR6jkPFKs +oBVZUZeJAHhIZ7p/YJGTowAM/u2+09fZOWfXYuEPYJU9AW7PsFo0hQ9xIzDwqMZkwS5 ZKyFgWVZcrhsDk4NfUx0lCx1aNKh5QbxNnRWMQagR4xiieZIik8XahFJX37aoccLRl2O Dvzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to; bh=LCMYNfRoPjnLFqVM6T+H24UaO9c7oj2RTAEpWJst5aQ=; b=uUB6uvfDzuoZQ7htvWmxuii8Lw2J/2ptlefm/OvGPcWJHOpQFGn7ojb22MP+uuMyzY vNDCppmSfXZTy/yGYJaZR0EJ3PT29IfgAojr2xA69MZhnJNpTa6RDde9CvAHjDNjgdZD x0hpmrKORLQn79ovupGCAseBuarpUTiF1yJoX3XqwqcqGhS5xrj4D7EXO/+SLx1Zo2Zd XYhcF3PQceJrrb2246lYIvRxwG+MTu/UeZ6s/CbLPNfy9lEg85OA+Mo7eINzkoE8wSaQ rIZW2GCPM0QIR4Pd0RWKvFl99m3/SI0hrxTFo8Bzy+FudM41lxbCTRJkZEJ/Q56twZ2h 78vA== X-Gm-Message-State: AIkVDXJ/6mRdPgQ4jJh8v1i/HwXd5XX7GekJ3L4XPYYm1rR3SnxAx/qlBSFNN/s5HVUndw== X-Received: by 10.176.22.136 with SMTP id e8mr3939629uaf.154.1484831077489; Thu, 19 Jan 2017 05:04:37 -0800 (PST) Received: from [192.168.0.9] ([191.190.196.248]) by smtp.gmail.com with ESMTPSA id z79sm1328284vkd.12.2017.01.19.05.04.36 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 19 Jan 2017 05:04:37 -0800 (PST) Subject: Re: Optimization inner join To: phillip@couto.in References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com> <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net> Cc: "pgsql-performance@postgresql.org" From: Clailson Message-ID: <2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com> Date: Thu, 19 Jan 2017 10:04:28 -0300 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101 Thunderbird/45.6.0 MIME-Version: 1.0 In-Reply-To: <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net> Content-Type: multipart/alternative; boundary="------------5E61C633E614D4A73C311BF4" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------5E61C633E614D4A73C311BF4 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit Hi Phillip. > 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. a.a is the primary key on table a and b.b is the foreign key on table b. Tabela "public.a" +--------+---------+---------------+ | Coluna | Tipo | Modificadores | +--------+---------+---------------+ | a | integer | não nulo | | b | integer | | +--------+---------+---------------+ Índices: "a_pkey" PRIMARY KEY, btree (a) Referenciada por: TABLE "b" CONSTRAINT "b_b_fkey" FOREIGN KEY (b) REFERENCES a(a) Tabela "public.b" +--------+---------+---------------+ | Coluna | Tipo | Modificadores | +--------+---------+---------------+ | a | integer | não nulo | | b | integer | | +--------+---------+---------------+ Índices: "b_pkey" PRIMARY KEY, btree (a) Restrições de chave estrangeira: "b_b_fkey" FOREIGN KEY (b) REFERENCES a(a) > > Not sure if it is all that common. Curious what if you put b.b IS NOT > NULL in the WHERE statement? It's the question. In the company I work with, one of my clients asked me: "Why PostgreSQL does not remove rows with null in column b (table b), before joining, since these rows have no corresponding in table a?" I gave the suggestion to put the IS NOT NULL in the WHERE statement, but HE can't modify the query in the application. I did the tests with Oracle and it uses a predicate in the query plan, removing the lines where b.b is null. In Oracle, it´s the same plan, with and without IS NOT NULL in the WHERE statement. -- Clailson Soares Dinízio de Almeida On 19/01/2017 09:34, Phillip Couto wrote: > 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 > > 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 > --------------5E61C633E614D4A73C311BF4 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit Hi Phillip.

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.
a.a is the primary key on table a and b.b is the foreign key on table b.

 Tabela "public.a" 
+--------+---------+---------------+
| Coluna |  Tipo   | Modificadores |
+--------+---------+---------------+
| a      | integer | não nulo      |
| b      | integer |               |
+--------+---------+---------------+
Índices:
    "a_pkey" PRIMARY KEY, btree (a)
Referenciada por:
    TABLE "b" CONSTRAINT "b_b_fkey" FOREIGN KEY (b) REFERENCES a(a)

         Tabela "public.b" 
+--------+---------+---------------+
| Coluna |  Tipo   | Modificadores |
+--------+---------+---------------+
| a      | integer | não nulo      |
| b      | integer |               |
+--------+---------+---------------+
Índices:
    "b_pkey" PRIMARY KEY, btree (a)
Restrições de chave estrangeira:
    "b_b_fkey" FOREIGN KEY (b) REFERENCES a(a)

Not sure if it is all that common. Curious what if you put b.b IS NOT NULL in the WHERE statement?

It's the question. In the company I work with, one of my clients asked me: "Why PostgreSQL does not remove rows with null in column b (table b), before joining, since these rows have no corresponding in table a?" I gave the suggestion to put the IS NOT NULL in the WHERE statement, but HE can't modify the query in the application.

I did the tests with Oracle and it uses a predicate in the query plan, removing the lines where b.b is null. In Oracle, it´s the same plan, with and without IS NOT NULL in the WHERE statement.
 
-- 
Clailson Soares Dinízio de Almeida

On 19/01/2017 09:34, Phillip Couto wrote:
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


--------------5E61C633E614D4A73C311BF4--