On Thu, Nov 30, 2017 at 4:08 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Ashutosh Bapat <ashutosh.ba...@enterprisedb.com> writes: >> On Fri, Jul 21, 2017 at 4:10 AM, Thomas Munro >> <thomas.mu...@enterprisedb.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
