From: | Clailson <clailson(dot)dba(at)gmail(dot)com> |
---|---|
To: | phillip(at)couto(dot)in |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Optimization inner join |
Date: | 2017-01-19 13:04:28 |
Message-ID: | 2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)dba(at)gmail(dot)com
>> <mailto:clailson(dot)dba(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Phillip Couto | 2017-01-19 13:18:19 | Re: Optimization inner join |
Previous Message | Phillip Couto | 2017-01-19 12:34:57 | Re: Optimization inner join |