\xC1\xBA\xFFВадим Акбашм\xAF\xF5в <ufaowl@gmail.com> writes: > I have encountered a problem with querry plan building: > I'd set default_statistics_target=700, run analyze. Postgres optimize had > chosen plan with hash_join and it took ~1 min for qerry to complete. > Then i set default_statistics_target=500 and the plan was significantly > changed and was using merge_join instead, complition time reduced in > hundreds times, cost reduced drastically. > Now i can't understand why more precise statistics leads to less optimized > plan and what is the right way to use default_statistics_target parameter? > I attach both good and bad querry plans and the querry itself
Are those really the same query? Plan 2 is enforcing a "number_value IS NOT NULL" condition on "attribute_value av1" that I don't see in plan 1. And neither plan seems to have much to do with the query, since the query has UNIONs that aren't in the plans.
But the short answer seems to be that in both cases, the only reason that the plan doesn't take forever to run is that one sub-join chances to yield precisely zero rows, and the PG executor happens to be more efficient about that corner case in the one plan shape than the other. The planner doesn't take the possibility of that short-circuit happening into account, since it generally cannot be sure that a sub-join wouldn't yield any rows. So it's just luck that one plan is noticeably faster in this case.
