Search Postgresql Archives

Re: Slow query when the select list is big

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux