Re: Endless loop in ExecNestLoop

> > The explain analyze output shows two levels of hash join underneath
> > four levels of nestloop join, whereas the stack trace looks like there
> > are five levels of nestloop and only one hash. So this is some evidence
> > that a different plan is being used in the slow cases. The stack trace
> > doesn't provide nearly enough info about what that plan is, though.
>
> What reasons are there for the planer to choose a different plan ?
>

Well after more research it turned out that sometimes we have a plan
that executes in less than 2 seconds, and somethimes we get a plan
does not terminate within 1h30 of CPU time.

The only thing that is to mention about the planer configuration is, that
we disabled the geqo.

What does influence the decisions of the planer ?
What can we do to make the planers decisions more exact ?

The cost estimation of the two plans shows only a very little difference,
while in real, it difference is very significant.

The good plan:

Sort (cost=12862.86..12862.87 rows=1 width=483)
Sort Key: con.shortname
-> Nested Loop (cost=10111.09..12862.85 rows=1 width=483)
-> Nested Loop (cost=10109.09..12859.83 rows=1 width=478)
-> Nested Loop (cost=10109.09..12856.79 rows=1 width=129)
-> Nested Loop (cost=10109.09..12853.76 rows=1 width=115)
-> Merge Join (cost=10109.09..12850.69 rows=1 width=88)
Merge Cond: ("outer".objid = "inner".cel_objid)
-> Nested Loop Left Join (cost=1.15..5719.95 rows=459 width=28)
Join Filter: ("inner".objid = "outer".ser_objid)
-> Index Scan using contractelements_pkey on contractelements cel (cost=0.00..5574.21 rows=459 width=22)
Filter: (upper((isactiv)::text) = 'Y'::text)
-> Materialize (cost=1.15..1.29 rows=14 width=14)
-> Seq Scan on servicetypes ser (cost=0.00..1.14 rows=14 width=14)
-> Sort (cost=10107.93..10107.94 rows=1 width=64)
Sort Key: dev.cel_objid
-> Seq Scan on devices dev (cost=0.00..10107.92 rows=1 width=64)
Filter: ((upper((COALESCE(isactiv, 'Y'::character varying))::text) = 'Y'::text) AND (upper((COALESCE(iscommittedsp, 'Y'::character varying))::text) = 'Y'::text) AND (upper((COALESCE(iscommittedcust, 'Y'::character varying))::text) = 'Y'::text))
-> Index Scan using contracts_pkey on contracts con (cost=0.00..3.06 rows=1 width=35)
Index Cond: ("outer".con_objid = con.objid)
Filter: ((ccu_objid = 3837149) OR (ccu_objid = 3837320) OR (ccu_objid = 3837375) OR (ccu_objid = 3974872) OR (ccu_objid = 5032031) OR (ccu_objid = 7220313) OR (ccu_objid = 15684899) OR (ccu_objid = 16052168) OR (ccu_objid = 19196573) OR (ccu_objid = 20095712) OR (ccu_objid = 22399813) OR (ccu_objid = 42092332) OR (ccu_objid = 42092454) OR (ccu_objid = 42092574) OR (ccu_objid = 42886947) OR (ccu_objid = 43813234))
-> Index Scan using devicetypes_pkey on devicetypes dty (cost=0.00..3.02 rows=1 width=18)
Index Cond: (dty.objid = "outer".dty_objid)
-> Index Scan using locations_pkey on locations loc (cost=0.00..3.03 rows=1 width=353)
Index Cond: (loc.objid = "outer".loc_objid)
Filter: (upper((shortname)::text) = '5195'::text)
-> Bitmap Heap Scan on bpartners man (cost=2.00..2.97 rows=1 width=13)
Recheck Cond: (man.objid = "outer".bpa_objid)
-> Bitmap Index Scan on bpartners_pkey (cost=0.00..2.00 rows=1 width=0)
Index Cond: (man.objid = "outer".bpa_objid)
(30 rows)

The bad plan:

Sort (cost=16257.75..16257.76 rows=1 width=262)
Sort Key: con.shortname
-> Nested Loop (cost=1.18..16257.74 rows=1 width=262)
-> Nested Loop (cost=1.18..16254.57 rows=1 width=130)
-> Nested Loop (cost=1.18..16251.50 rows=1 width=125)
-> Nested Loop (cost=1.18..16248.45 rows=1 width=111)
-> Nested Loop (cost=1.18..16245.31 rows=1 width=86)
Join Filter: ("outer".cel_objid = "inner".objid)
-> Seq Scan on devices dev (cost=0.00..11984.25 rows=1 width=63)
Filter: ((upper((COALESCE(isactiv, 'Y'::character varying))::text) = 'Y'::text) AND (upper((COALESCE(iscommittedsp, 'Y'::character varying))::text) = 'Y'::text) AND (upper((COALESCE(iscommittedcust, 'Y'::character varying))::text) = 'Y'::text))
-> Hash Left Join (cost=1.18..4256.22 rows=387 width=27)
Hash Cond: ("outer".ser_objid = "inner".objid)
-> Seq Scan on contractelements cel (cost=0.00..4253.10 rows=387 width=21)
Filter: (upper((isactiv)::text) = 'Y'::text)
-> Hash (cost=1.14..1.14 rows=14 width=14)
-> Seq Scan on servicetypes ser (cost=0.00..1.14 rows=14 width=14)
-> Index Scan using contracts_pkey on contracts con (cost=0.00..3.13 rows=1 width=33)
Index Cond: ("outer".con_objid = con.objid)
Filter: ((ccu_objid = 3837149) OR (ccu_objid = 3837320) OR (ccu_objid = 3837375) OR (ccu_objid = 3974872) OR (ccu_objid = 5032031) OR (ccu_objid = 7220313) OR (ccu_objid = 15684899) OR (ccu_objid = 16052168) OR (ccu_objid = 19196573) OR (ccu_objid = 20095712) OR (ccu_objid = 22399813) OR (ccu_objid = 42092332) OR (ccu_objid = 42092454) OR (ccu_objid = 42092574) OR (ccu_objid = 42886947) OR (ccu_objid = 43813234))
-> Index Scan using devicetypes_pkey on devicetypes dty (cost=0.00..3.04 rows=1 width=18)
Index Cond: (dty.objid = "outer".dty_objid)
-> Index Scan using bpartners_pkey on bpartners man (cost=0.00..3.06 rows=1 width=13)
Index Cond: (man.objid = "outer".bpa_objid)
-> Index Scan using locations_pkey on locations loc (cost=0.00..3.12 rows=1 width=136)
Index Cond: (loc.objid = "outer".loc_objid)
Filter: (upper((shortname)::text) = '5195'::text)

-philipp
--
: Dipl-Ing Philipp Reisner Tel +43-1-8178292-50 :
: LINBIT Information Technologies GmbH Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria http://www.linbit.com :