Re: Slow query when the select list is big
От | Sterpu Victor |
---|---|
Тема | Re: Slow query when the select list is big |
Дата | |
Msg-id | em715e9cd8-a78b-4438-bd56-48173a0d4ee1@victor-pc обсуждение исходный текст |
Ответ на | Re: Slow query when the select list is big (David Rowley <david.rowley@2ndquadrant.com>) |
Ответы |
Re: Slow query when the select list is big
|
Список | pgsql-general |
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@2ndquadrant.com> To: "Sterpu Victor" <victor@caido.ro> Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General" <pgsql-general@postgresql.org>; "David G. Johnston" <david.g.johnston@gmail.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.g.johnston@gmail.com> >wrote: >> On Sunday, May 8, 2016, Sterpu Victor <victor@caido.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
В списке pgsql-general по дате отправления: