Re: join estimate of subqueries with range conditions and constraint exclusion

On Wed, May 24, 2017 at 2:17 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> We got bitten again by what appears to be the same issue I reported
> (perhaps
> poorly) here:
> https://www.postgresql.org/message-id/20170326193344.
> GS31628%40telsasoft.com
>
> We have PG9.6.3 table heirarchies partitioned by time. Our reports use
> subqueries each with their own copies of a range clauses on time column, as
> needed to get constraint exclusion reference:
> https://www.postgresql.org/message-id/25076.1366321335%40sss.pgh.pa.us
>
> SELECT * FROM
> (SELECT * FROM t WHERE col>const) a JOIN
> (SELECT * FROM t WHERE col>const) b USING (col)
>
> I'm diagnosing a bad estimate/plan due to excessively high n_distinct
> leading
> to underestimated rowcount when selecting from a small fraction of the
> table
> heirarchy. This leads intermittently to bad things, specifically a
> cascade of
> misestimates and associated nested loops around millions of rows.
>

​Justin,

I'm not going to be much help personally but I just wanted to say that with
PGCon just completed and Beta1 just starting, combined with the somewhat
specialized nature of the problem, a response should be forthcoming even
though its taking a bit longer than usual.

David J.