Re: Optimizer issue -- bad query plan?

On Thu, Jun 5, 2014 at 7:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

“Quality is not an act, it is a habit.” — Aristotle
​