Em 19/01/2017 12:13, Tom Lane escreveu:
Gustavo Rezende Montesino writes:
Being the client in question, I would like to make a little remark: What
we thought could be optimized here at first is on the row estimate of
the index scan; which could take null_frac into account. To put things
into pe
The picture is becoming clearer now. So to recap the issue is in the plan
selection not utilizing the null_frac statistic properly to skip what seems to
be in your case 99% of the rows which are NULL for the field the join is
happening on and would be discarded anyways.
For completeness do you
Gustavo Rezende Montesino writes:
> Being the client in question, I would like to make a little remark: What
> we thought could be optimized here at first is on the row estimate of
> the index scan; which could take null_frac into account. To put things
> into perspective, our similar case in p
I apologize my statement about NULL being used to join is incorrect as both
Vitalii and Gustavo have both pointed out in their respective replies.
-
Phillip Couto
> On Jan 19, 2017, at 08:30, Vitalii Tymchyshyn wrote:
>
>
> Hi.
>
> In SQL "null == any value" resolves to fal
Hi.
In SQL "null == any value" resolves to false, so optimizer can safely skip
nulls from either side if any for the inner join.
Best regards, Vitalii Tymchyshyn
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
>
Hello,
Em 19/01/2017 11:04, Clailson escreveu:
Hi Phillip.
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 colum
Ah ok that makes sense. I am curious if there is actually a performance benefit
to doing that. In postgresql as per the execution plan you provided the Merge
Join joins both sets after the have been sorted. If they are sorted already
then the NULLs will all be grouped at the beginning or end. (C
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
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?
-
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=
10 matches
Mail list logo