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.
