Re: Endless loop in ExecNestLoop - Mailing list pgsql-bugs

> > 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=20
that executes in less than 2 seconds, and somethimes we get a plan=20
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.=20

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=3D12862.86..12862.87 rows=3D1 width=3D483)
   Sort Key: con.shortname
   ->  Nested Loop  (cost=3D10111.09..12862.85 rows=3D1 width=3D483)
         ->  Nested Loop  (cost=3D10109.09..12859.83 rows=3D1 width=3D478)
               ->  Nested Loop  (cost=3D10109.09..12856.79 rows=3D1 width=
=3D129)
                     ->  Nested Loop  (cost=3D10109.09..12853.76 rows=3D1 w=
idth=3D115)
                           ->  Merge Join  (cost=3D10109.09..12850.69 rows=
=3D1 width=3D88)
                                 Merge Cond: ("outer".objid =3D "inner".cel=
_objid)
                                 ->  Nested Loop Left Join  (cost=3D1.15..5=
719.95 rows=3D459 width=3D28)
                                       Join Filter: ("inner".objid =3D "out=
er".ser_objid)
                                       ->  Index Scan using contractelement=
s_pkey on contractelements cel  (cost=3D0.00..5574.21 rows=3D459 width=3D22)
                                             Filter: (upper((isactiv)::text=
) =3D 'Y'::text)
                                       ->  Materialize  (cost=3D1.15..1.29 =
rows=3D14 width=3D14)
                                             ->  Seq Scan on servicetypes s=
er  (cost=3D0.00..1.14 rows=3D14 width=3D14)
                                 ->  Sort  (cost=3D10107.93..10107.94 rows=
=3D1 width=3D64)
                                       Sort Key: dev.cel_objid
                                       ->  Seq Scan on devices dev  (cost=
=3D0.00..10107.92 rows=3D1 width=3D64)
                                             Filter: ((upper((COALESCE(isac=
tiv, 'Y'::character varying))::text) =3D 'Y'::text) AND (upper((COALESCE(is=
committedsp, 'Y'::character varying))::text) =3D 'Y'::text) AND (upper((COA=
LESCE(iscommittedcust, 'Y'::character varying))::text) =3D 'Y'::text))
                           ->  Index Scan using contracts_pkey on contracts=
 con  (cost=3D0.00..3.06 rows=3D1 width=3D35)
                                 Index Cond: ("outer".con_objid =3D con.obj=
id)
                                 Filter: ((ccu_objid =3D 3837149) OR (ccu_o=
bjid =3D 3837320) OR (ccu_objid =3D 3837375) OR (ccu_objid =3D 3974872) OR =
(ccu_objid =3D 5032031) OR (ccu_objid =3D 7220313) OR (ccu_objid =3D 156848=
99) OR (ccu_objid =3D 16052168) OR (ccu_objid =3D 19196573) OR (ccu_objid =
=3D 20095712) OR (ccu_objid =3D 22399813) OR (ccu_objid =3D 42092332) OR (c=
cu_objid =3D 42092454) OR (ccu_objid =3D 42092574) OR (ccu_objid =3D 428869=
47) OR (ccu_objid =3D 43813234))
                     ->  Index Scan using devicetypes_pkey on devicetypes d=
ty  (cost=3D0.00..3.02 rows=3D1 width=3D18)
                           Index Cond: (dty.objid =3D "outer".dty_objid)
               ->  Index Scan using locations_pkey on locations loc  (cost=
=3D0.00..3.03 rows=3D1 width=3D353)
                     Index Cond: (loc.objid =3D "outer".loc_objid)
                     Filter: (upper((shortname)::text) =3D '5195'::text)
         ->  Bitmap Heap Scan on bpartners man  (cost=3D2.00..2.97 rows=3D1=
 width=3D13)
               Recheck Cond: (man.objid =3D "outer".bpa_objid)
               ->  Bitmap Index Scan on bpartners_pkey  (cost=3D0.00..2.00 =
rows=3D1 width=3D0)
                     Index Cond: (man.objid =3D "outer".bpa_objid)
(30 rows)


The bad plan:

Sort  (cost=3D16257.75..16257.76 rows=3D1 width=3D262)
   Sort Key: con.shortname
   ->  Nested Loop  (cost=3D1.18..16257.74 rows=3D1 width=3D262)
         ->  Nested Loop  (cost=3D1.18..16254.57 rows=3D1 width=3D130)
               ->  Nested Loop  (cost=3D1.18..16251.50 rows=3D1 width=3D125)
                     ->  Nested Loop  (cost=3D1.18..16248.45 rows=3D1 width=
=3D111)
                           ->  Nested Loop  (cost=3D1.18..16245.31 rows=3D1=
 width=3D86)
                                 Join Filter: ("outer".cel_objid =3D "inner=
".objid)
                                 ->  Seq Scan on devices dev  (cost=3D0.00.=
.11984.25 rows=3D1 width=3D63)
                                       Filter: ((upper((COALESCE(isactiv, '=
Y'::character varying))::text) =3D 'Y'::text) AND (upper((COALESCE(iscommit=
tedsp, 'Y'::character varying))::text) =3D 'Y'::text) AND (upper((COALESCE(=
iscommittedcust, 'Y'::character varying))::text) =3D 'Y'::text))
                                 ->  Hash Left Join  (cost=3D1.18..4256.22 =
rows=3D387 width=3D27)
                                       Hash Cond: ("outer".ser_objid =3D "i=
nner".objid)
                                       ->  Seq Scan on contractelements cel=
  (cost=3D0.00..4253.10 rows=3D387 width=3D21)
                                             Filter: (upper((isactiv)::text=
) =3D 'Y'::text)
                                       ->  Hash  (cost=3D1.14..1.14 rows=3D=
14 width=3D14)
                                             ->  Seq Scan on servicetypes s=
er  (cost=3D0.00..1.14 rows=3D14 width=3D14)
                           ->  Index Scan using contracts_pkey on contracts=
 con  (cost=3D0.00..3.13 rows=3D1 width=3D33)
                                 Index Cond: ("outer".con_objid =3D con.obj=
id)
                                 Filter: ((ccu_objid =3D 3837149) OR (ccu_o=
bjid =3D 3837320) OR (ccu_objid =3D 3837375) OR (ccu_objid =3D 3974872) OR =
(ccu_objid =3D 5032031) OR (ccu_objid =3D 7220313) OR (ccu_objid =3D 156848=
99) OR (ccu_objid =3D 16052168) OR (ccu_objid =3D 19196573) OR (ccu_objid =
=3D 20095712) OR (ccu_objid =3D 22399813) OR (ccu_objid =3D 42092332) OR (c=
cu_objid =3D 42092454) OR (ccu_objid =3D 42092574) OR (ccu_objid =3D 428869=
47) OR (ccu_objid =3D 43813234))
                     ->  Index Scan using devicetypes_pkey on devicetypes d=
ty  (cost=3D0.00..3.04 rows=3D1 width=3D18)
                           Index Cond: (dty.objid =3D "outer".dty_objid)
               ->  Index Scan using bpartners_pkey on bpartners man  (cost=
=3D0.00..3.06 rows=3D1 width=3D13)
                     Index Cond: (man.objid =3D "outer".bpa_objid)
         ->  Index Scan using locations_pkey on locations loc  (cost=3D0.00=
..3.12 rows=3D1 width=3D136)
               Index Cond: (loc.objid =3D "outer".loc_objid)
               Filter: (upper((shortname)::text) =3D '5195'::text)

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