Re: nested loop semijoin estimates

On 06/02/15 16:37, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>> OK, so I did the testing today - with TPC-H and TPC-DS benchmarks. The
>> results are good, IMHO.
>
> I'm a bit disturbed by that, because AFAICS from the plans, these
> queries did not involve any semi or anti joins, which should mean
> that the patch would not have changed the planner's behavior. You
> were using the second patch as-posted, right, without further hacking
> on compare_path_costs_fuzzily?

Yes, no additional changes.

> It's possible that the change was due to random variation in ANALYZE
> statistics, in which case it was just luck.

I don't think so. I simply loaded the data, ran ANALYZE, and then simply
started either master or patched master. There should be no difference
in statistics, I believe. Also, the plans contain pretty much the same
row counts, but the costs differ.

For example look at the 'cs_ui' CTE, right at the beginning of the
analyze logs. The row counts are exactly the same, but the costs are
different. And it's not using semijoins or not nested loops ...

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services