Обсуждение: 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 Thu, Jun 5, 2014 at 7:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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