Re: Optimizer issue -- bad query plan?

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