Optimizer issue -- bad query plan?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jun 5, 2014 at 6:09 PM, John R Pierce <pierce@hogranch.com> wrote:

why would you join 28 tables you're not using ??

The query is derived from a view. I want to select only one column from a
view with many columns. I figured the optimizer would be smart enough in
this case not to join all of the tables needed for the columns I wasn't
referencing at all.

I thought it might be that postgres couldn't optimize the query due to the
fact that it was a view, so that's why I pulled out this portion to see if
it would speed up. But it didn't.

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com&gt;
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"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&#39;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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

I'm running 9.3.4.

https://wiki.postgresql.org/wiki/What&#39;s_new_in_PostgreSQL_9.0

Read the section named "Join Removal".

Right, I'm aware of this feature -- I'm just curious why it's not working!

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com&gt;
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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. <http://www.neadwerx.com&gt;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general