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.rowley@xxxxxxxxxxxxxxx>
To: "Sterpu Victor" <victor@xxxxxxxx>
Cc: "Rob Imig" <rimig88@xxxxxxxxx>; "PostgreSQL General"
<pgsql-general@xxxxxxxxxxxxxx>; "David G. Johnston"
<david.g.johnston@xxxxxxxxx>
Sent: 9/5/2016 10:04:54 AM
Subject: Re: Slow query when the select list is big
On 9 May 2016 at 18:46, David G. Johnston <david.g.johnston@xxxxxxxxx>
wrote:
On Sunday, May 8, 2016, Sterpu Victor <victor@xxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
