🚀 go-pugleaf

Karol Bieniaszewski wrote:
>
> FROM
> TABLE_B B
> INNER JOIN TABLE_A A ON B.ID_OBJ=A.ID
> INNER JOIN TABLE_C C ON C.ID_OBJ=A.ID AND B.SYSTEM=C.SYSTEM AND
> C.ZRODLO=:PARAM_X
> WHERE
> B.KOD=PARAM_Y
> AND B.SYSTEM=PARAM_Z

Don't know if it will change anything, but "C.ZRODLO=:PARAM_X" should be in 
the Where clause, not in the join condition.

> FROM
> TABLE_B B
> INNER JOIN TABLE_A A ON B.ID_OBJ=A.ID
> INNER JOIN (TABLE_C C LEFT JOIN TABLE_D D ON C.ALARM=D.NAZWA) ON
> C.ID_OBJ=A.ID AND B.SYSTEM=C.SYSTEM AND C.ZRODLO=:PARAM_X
> WHERE
> B.KOD=PARAM_Y
> AND B.SYSTEM=PARAM_Z

I have never seen any advantage in nesting joins, breaking them out as you 
do below is more readable, and, IME, has no negative effect on performance.

> FROM
> TABLE_B B
> INNER JOIN TABLE_A A ON B.ID_OBJ=A.ID
> INNER JOIN TABLE_C C  ON C.ID_OBJ=A.ID AND B.SYSTEM=C.SYSTEM AND
> C.ZRODLO=:PARAM_X
> LEFT JOIN TABLE_D D ON C.ALARM=D.NAZWA
> WHERE
> B.KOD=PARAM_Y
> AND B.SYSTEM=PARAM_Z
>
> but without success
> now A is NATURAL

A natural scan on A is more easily understandable here because you clearly 
have no conditions on table A, thus it makes more sense to simply scan it 
and perform indexed lookups in the other tables. What performance are you 
getting with this version compared to any of the others?

I would try this:

FROM
TABLE_C C
INNER JOIN TABLE_A A ON A.ID=C.ID_OBJ
INNER JOIN TABLE_B B ON B.ID_OBJ=C.ID_OBJ AND B.SYSTEM=C.SYSTEM
LEFT JOIN TABLE_D D ON D.NAZWA=C.ALARM
WHERE
C.ZRODLO=:PARAM_X
AND B.KOD=:PARAM_Y
AND B.SYSTEM=:PARAM_Z

How does that change the plan and performance?

-- 
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"I would remind you that extremism in the defense of liberty is no
vice! And let me remind you also that moderation in the pursuit of
justice is no virtue!" - Barry Goldwater