Re: [HACKERS] <> join selectivity estimate question

On Fri, Dec 1, 2017 at 4:05 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Nov 29, 2017 at 11:55 PM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>> 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.
>
> Hmm, do you have an example of the better but still-funky estimates
> handy? Like an EXPLAIN plan?

Sure. Here's some EXPLAIN ANALYZE output from scale 3 TPCH + a few
indexes[1]. There's a version from HEAD with and without commit
7ca25b7d.

[1] https://github.com/macdice/pg_sisyphus/blob/master/cluster-recipes/make-tpch-cluster.sh

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