Optimizer issue -- bad query plan? - Mailing list pgsql-general

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