Обсуждение: Optimizer issue -- bad query plan?
I have the following query:
SELECT r.reset FROM tb_reset r LEFT JOIN tb_project pj ON pj.project = r.project LEFT JOIN tb_location l ON l.location = r.location LEFT JOIN tb_program pg ON pg.program = r.program LEFT JOIN tb_reset_survey rsv ON rsv.reset = r.reset LEFT JOIN tb_program_location pl ON pl.program = r.program AND pl.location = r.location LEFT JOIN tb_program_type pgt ON pgt.program_type = pg.program_type LEFT JOIN tb_project_department pd ON pd.project = pj.project LEFT JOIN tb_department d ON d.department = pd.department LEFT JOIN tb_fiscal_calendar fc ON fc.day = r.execution_date LEFT JOIN tb_fiscal_month fm ON fm.fiscal_month = fc.month LEFT JOIN tb_rollout_schedule rs ON rs.fiscal_week = fc.week AND rs.fiscal_year = fc.year AND rs.program = r.program LEFT JOIN tb_reset_team rt ON rt.reset_team = r.reset_team LEFT JOIN tb_vendor v ON v.vendor = rt.vendor LEFT JOIN tb_reset_status rst ON rst.reset_status = r.reset_status LEFT JOIN tb_order_location ol ON ol.location = r.location LEFT JOIN tb_entity_reset er ON er.reset = r.reset LEFT JOIN tb_market m ON m.market = l.market LEFT JOIN tb_district dist ON dist.district = l.district LEFT JOIN tb_mregion mr ON mr.mregion = l.mregion LEFT JOIN tb_region rg ON rg.region = l.region LEFT JOIN tb_ogrp ogrp ON ogrp.ogrp = l.ogrp LEFT JOIN tb_buying_office byo ON byo.buying_office = l.buying_office LEFT JOIN tb_project_participant pp ON pp.project = r.project LEFT JOIN tb_project_status ps ON ps.project_status = pj.project_status LEFT JOIN tb_entity_location el_mem ON el_mem.location = r.location AND el_mem.role = 30 LEFT JOIN tb_entity_reset er_fcpm ON er_fcpm.reset = r.reset AND er_fcpm.role = 74 LEFT JOIN tb_entity_location el_fss ON el_fss.location = r.location AND el_fss.role = 35 LEFT JOIN tb_project_participant pp_ipm ON pp_ipm.project = r.project AND pp_ipm.role = 3
where r.in_scope is true
and r.project = 18922
group by r.reset
order by r.reset
limit 100 offset 0;
The EXPLAIN ANALYZE for this query indicates that all of the tables in the query are being joined despite the fact that they are not needed at all.
Why is this?
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
“Quality is not an act, it is a habit.” — Aristotle
On 6/5/2014 2:43 PM, Moshe Jacobson wrote: > > The EXPLAIN ANALYZE for this query indicates that all of the tables in > the query are being joined despite the fact that they are not needed > at all. > > Why is this? > why would you join 28 tables you're not using ?? -- john r pierce 37N 122W somewhere on the middle of the left coast
why would you join 28 tables you're not using ??
Moshe Jacobson
"Quality is not an act, it is a habit." -- Aristotle
Moshe Jacobson wrote > I have the following query: > [...] > The EXPLAIN ANALYZE for this query indicates that all of the tables in the > query are being joined despite the fact that they are not needed at all. > > Why is this? Without definitions of all the tables involved, as well as knowing what version you are running this query on, it is impossible to explain why. https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.0 Read the section named "Join Removal". It would seem that in theory at least some of these could be removed - though maybe not all of them - if you are running 9.0+ (possibly after some schema changes). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Optimizer-issue-bad-query-plan-tp5806260p5806264.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Without definitions of all the tables involved, as well as knowing what
version you are running this query on, it is impossible to explain why.
https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.0
Read the section named "Join Removal".
Moshe Jacobson
"Quality is not an act, it is a habit." -- Aristotle
Moshe Jacobson <moshe@neadwerx.com> writes: > On Thu, Jun 5, 2014 at 6:38 PM, David G Johnston <david.g.johnston@gmail.com >> wrote: >> Without definitions of all the tables involved, as well as knowing what >> version you are running this query on, it is impossible to explain why. > Right, I'm aware of this feature -- I'm just curious why it's not working! So are we, but you've not provided a self-contained example. I suspect that there's something you haven't shown us that means that the view subquery doesn't get flattened into the calling query, so that the planner won't be aware while planning the subquery that not all its output columns are actually used. But that's just a guess unsupported by evidence. We need to see the actual view definition and calling query, not a simplified "equivalent" query. regards, tom lane
On Thu, Jun 5, 2014 at 7:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We need to see the actual view definition and calling query,
not a simplified "equivalent" query.
The simple query has the same performance issues as the view. What help would it be to see the view?
I can provide any other information you want, just let me know.
In case you still want it, here is the view definition, though I think it's irrelevant.
SELECT r.reset, l.number, pj.name AS project_name, r.location, r.reset_team, r.project, r.program, r.wbse, r.in_scope, r.bay_count, r.labor_duration, r.execution_date, r.creator, r.prewalk_due_date, r.prewalk_duration, r.planogram_url, r.signoff_received, r.reset_status, v.name AS vendor_name, rst.label AS reset_status_label, pg.name AS program_name, pg.fiscal_year, rsv.submitted, CASE WHEN rsv.reset_survey IS NOT NULL THEN r.prewalk_due_date ELSE NULL::date END AS prewalk_date, d.number AS department_number, CASE WHEN pg.program_type = 14 THEN pj.rollout_date WHEN pg.program_type = 9 THEN r.cet_wave_date ELSE pl.execution_date END AS reset_start_date, pg.program_type, pj.project_type, pj.project_status, rs.rollout_week, l.region, l.ogrp, l.market, l.buying_office, l.district, l.mregion, l.location_type, ol.order_location, pp.entity AS project_participant_entity, pp.role AS project_participant_role, d.department, v.vendor, pj.archived AS project_archived, r.archived AS reset_archived, rt.labor_team_type, el_mem.entity AS entity_30, er_fcpm.entity AS entity_74, el_fss.entity AS entity_35, pp_ipm.entity AS entity_3 FROM tb_reset r LEFT JOIN tb_project pj ON pj.project = r.project LEFT JOIN tb_location l ON l.location = r.location LEFT JOIN tb_program pg ON pg.program = r.program LEFT JOIN tb_reset_survey rsv ON rsv.reset = r.reset LEFT JOIN tb_program_location pl ON pl.program = r.program AND pl.location = r.location LEFT JOIN tb_program_type pgt ON pgt.program_type = pg.program_type LEFT JOIN tb_project_department pd ON pd.project = pj.project LEFT JOIN tb_department d ON d.department = pd.department LEFT JOIN tb_fiscal_calendar fc ON fc.day = r.execution_date LEFT JOIN tb_fiscal_month fm ON fm.fiscal_month = fc.month LEFT JOIN tb_rollout_schedule rs ON rs.fiscal_week = fc.week AND rs.fiscal_year = fc.year AND rs.program = r.program LEFT JOIN tb_reset_team rt ON rt.reset_team = r.reset_team LEFT JOIN tb_vendor v ON v.vendor = rt.vendor LEFT JOIN tb_reset_status rst ON rst.reset_status = r.reset_status LEFT JOIN tb_order_location ol ON ol.location = r.location LEFT JOIN tb_entity_reset er ON er.reset = r.reset LEFT JOIN tb_market m ON m.market = l.market LEFT JOIN tb_district dist ON dist.district = l.district LEFT JOIN tb_mregion mr ON mr.mregion = l.mregion LEFT JOIN tb_region rg ON rg.region = l.region LEFT JOIN tb_ogrp ogrp ON ogrp.ogrp = l.ogrp LEFT JOIN tb_buying_office byo ON byo.buying_office = l.buying_office LEFT JOIN tb_project_participant pp ON pp.project = r.project LEFT JOIN tb_project_status ps ON ps.project_status = pj.project_status LEFT JOIN tb_entity_location el_mem ON el_mem.location = r.location AND el_mem.role = 30 LEFT JOIN tb_entity_reset er_fcpm ON er_fcpm.reset = r.reset AND er_fcpm.role = 74 LEFT JOIN tb_entity_location el_fss ON el_fss.location = r.location AND el_fss.role = 35 LEFT JOIN tb_project_participant pp_ipm ON pp_ipm.project = r.project AND pp_ipm.role = 3;
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
“Quality is not an act, it is a habit.” — Aristotle
Moshe Jacobson <moshe@neadwerx.com> wrote: > I can provide any other information you want, just let me know. The best thing would be to show a *self-contained* test case -- that is, starting with an empty database create tables and (if needed) the view and show a query which demonstrates the problem, all in the form of a SQL script. Perhaps you only need to or three tables to demonstrate the effect; if you need more, that in itself is a valuable clue. If a developer can make the issue occur on their own machine, they can investigate much more effectively than without that. This is especially true when something works as expected in a number tests on a wide variety of environments; something must be different about your environment, tables, or queries -- but what? A self- contained test will make that clear. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company