Re: [HACKERS] <> join selectivity estimate question

From: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] <> join selectivity estimate question
Date: 2017-11-30 21:41:39
Message-ID: CAEepm=2XfyeuT_k5eh+dCSYs6q4OWTTZygBEobTw4QmK3ooNcA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
with-7ca25b7d.txt text/plain 5.1 KB
without-7ca25b7d.txt text/plain 5.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2017-11-30 21:48:23 Re: IndexTupleDSize macro seems redundant
Previous Message Alexander Korotkov 2017-11-30 21:24:10 Re: [HACKERS] [PATCH] Incremental sort