"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
> I currently have a strange behavior once statistics are collected. This is the statement (I don't know the
application,the statement is as it is):
I think your problem is with the subplan conditions, ie
> and (person1_.id in
> (select persons6_.per_id from pia_01.pesr_zielvergabe_person persons6_ where personziel3_.id =
persons6_.pes_id))
> ...
> and (personstan2_.id in
> (select stufen7_.id from pia_01.pess_person_stufe stufen7_ where personstan0_.id = stufen7_.pesz_id))
These essentially create weird join conditions (between person1_ and
personziel3_ in the first case or personstan2_ and personstan0_ in
the second case) that the planner has no idea how to estimate.
It just throws up its hands and uses a default selectivity of 0.5,
which is nowhere near reality in this case.
You accidentally got an acceptable (not great) plan anyway without
statistics, but not so much with statistics. Worse yet, the subplans
have to be implemented essentially as nestloop joins.
I'd suggest trying to flatten these to be regular joins, ie
try to bring up persons6_ and stufen7_ into the main JOIN nest.
It looks like persons6_.pes_id might be unique, meaning that you
don't really need the IN behavior in the first case so flattening
it should be straightforward. The other one is visibly not unique,
but since you're using "select distinct" at the top level anyway,
getting duplicate rows might not be a problem (unless there are
a lot of duplicates?)
regards, tom lane