PostgreSQL 9.4.x újabb hiba

Itt van egy beszélgetés a postgresql-es arcokkal. A lényeg, hogy amint a planner több infót kapott, végeredményül rosszabb döntést hozott, mint amit a plusz
info nélkül hozott volna, és ettől az egész belassult. Egy anti-join segítségével meg lehetett okosítani a plannert, és így megint sikerült jó döntést hoznia.

without analyze, the planner was (a) estimating that the table had more tuples than it actually had, and (b)
it had no idea how many matched 'Deleted', so it could only use a default guess

analyze, though, told it that there were only 4 tuples and all of them had 'Deleted'

so it assumes that the condition (s.status is distinct from 'Deleted') actually excluded almost everything

but in fact that condition is true for all the added null rows from the outer join, which the planner wasn't taking into account

the problem is that with an estimate of 1 at plan time, it thinks it's reasonable to use that as the outer path of a nestloop,
with an expensive plan on the inner path, estimating that the expensive plan is run only once

the real difference between the two plans is not the choice of index vs seqscan, but the order in which the joins are performed

the slow plan joins (persons,resourcestatus) against personinwindfarm

the fast one joins personinwindfarm against persons and then the result of that against resourcestatus

the difference being that the slow one is running the personinwindfarm and its associated subplan once for each person

whereas the fast one runs that once only, and hashjoins the result against persons

anyway, the workaround with not exists helps because the planner uses different code to estimate the selectivity of
something that it has identified as being an anti-join

and it knows that (bigtable antijoin smalltable on (unique column)) will preserve almost all the rows of bigtable

so, while the workaround isn't hard, making the planner use a correct estimate in the original case is likely to be tricky