Re: Slow query when the select list is big

I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if I
don't select from the joined tables.
Now is clear why the query is so mutch more efficient when I select less
data.

Thank you

------ Original Message ------
From: "David Rowley" <david(dot)rowley(at)2ndquadrant(dot)com>
To: "Sterpu Victor" <victor(at)caido(dot)ro>
Cc: "Rob Imig" <rimig88(at)gmail(dot)com>; "PostgreSQL General"
<pgsql-general(at)postgresql(dot)org>; "David G. Johnston"
<david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: 9/5/2016 10:04:54 AM
Subject: Re: [GENERAL] Slow query when the select list is big

>On 9 May 2016 at 18:46, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
>wrote:
>> On Sunday, May 8, 2016, Sterpu Victor <victor(at)caido(dot)ro> wrote:
>>>
>>> Yes but it is very big.
>>> I don't understand why the select list is influencing the CPU usage.
>>> I was expecting that only the join and where clauses would influence
>>>CPU.
>>>
>>
>> PostgreSQL is smart enough to optimize away stuff that it knows
>>doesn't
>> impact the final query result.
>
>To be more accurate with what David is saying, PostgreSQL will remove
>unused LEFT JOINed relations where the left joined relation can be
>proved to not duplicate rows from the right hand side. It would just
>be a matter of comparing the EXPLAINs from the query with all the
>SELECT items to the one with the single SELECT item to prove that this
>is what's happening.
>
>Please also note that this only occurs with LEFT JOINs
>
>It would also be quite helpful for people if you were to include a
>copy of the query. It's impossible to reverse engineer what that is
>from this EXPLAIN output. I see that your using a windowing function
>and performing a LIMIT 1, there may be ways to improve that just by
>selecting the single highest j1031101.validfrom row and performing the
>joins to the other table on that single row, but that will depend on
>which windowing function you're using as the function may require the
>other rows in the window frame to calculate the correct result.
>
>--
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services