Re: Strange influence of default_statistics_target

Hello.
Information i've sent, is it sufficient?
Thank you.

2017-01-19 14:11 GMT+05:00 Вадим Акбашев <ufaowl(at)gmail(dot)com>:

> Sorry, i've forgot to attach files themselves
>
> 2017-01-18 19:18 GMT+05:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>> =?UTF-8?B?0JLQsNC00LjQvCDQkNC60LHQsNGI0LXQsg==?= <ufaowl(at)gmail(dot)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.
>>
>> regards, tom lane
>>
>
>