Re: [PERFORM] Optimization inner join

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

Re: [PERFORM] Optimization inner join

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

Re: [PERFORM] Optimization inner join

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

Re: [PERFORM] Optimization inner join

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"

Re: [PERFORM] Optimization inner join

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 >

Re: [PERFORM] Optimization inner join

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

Re: [PERFORM] Optimization inner join

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.

Re: [PERFORM] Optimization inner join

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

Re: [PERFORM] Optimization inner join

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?