Re: [HACKERS] <> join selectivity estimate question

On Thu, Nov 30, 2017 at 4:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
>> On Fri, Jul 21, 2017 at 4:10 AM, Thomas Munro
>> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>>> Please find attached a new version, and a test script I used, which
>>> shows a bunch of interesting cases. I'll add this to the commitfest.
>
>> I added some "stable" tests to your patch taking inspiration from the
>> test SQL file. I think those will be stable across machines and runs.
>> Please let me know if those look good to you.
>
> This seems to have stalled on the question of what the regression tests
> should look like, which sems like a pretty silly thing to get hung up on
> when everybody agrees the patch itself is OK. I tried Ashutosh's proposed
> test cases and was pretty unimpressed after noting that they passed
> equally well against patched or unpatched backends. In any case, as noted
> upthread, we don't really like to expose exact rowcount estimates in test
> cases because of the risk of platform to platform variation. The more
> usual approach for checking whether the planner is making sane estimates
> is to find a query whose plan shape changes with or without the patch.
> I messed around a bit till I found such a query, and committed it.

Thank you for the original pointer and the commit. Everything here
seems to make intuitive sense and the accompanying throw-away tests
that I posted above seem to produce sensible results except in some
cases that we discussed, so I think this is progress. There is still
something pretty funny about the cardinality estimates for TPCH Q21
which I haven't grokked though. I suspect it is crafted to look for a
technique we don't know (an ancient challenge set by some long retired
database gurus back in 1992 that their RDBMSs know how to solve,
hopefully not in the manner of a certain car manufacturer's air
pollution tests), but I haven't yet obtained enough round tuits to dig
further. I will, though.

--
Thomas Munro
http://www.enterprisedb.com