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.