Re: neqjoinsel versus "refresh materialized view concurrently"

2018-03-19 Thread Tom Lane
Thomas Munro writes: > On Wed, Mar 14, 2018 at 2:56 PM, Jeff Janes wrote: >> Is there any good way to make the regression tests fail if the plan reverts >> to the bad one? The only thing I can think of would be to make the table >> bigger so

Re: neqjoinsel versus "refresh materialized view concurrently"

2018-03-14 Thread Thomas Munro
On Wed, Mar 14, 2018 at 2:56 PM, Jeff Janes wrote: > On Tue, Mar 13, 2018 at 4:57 PM, Thomas Munro > wrote: >> Here's a patch to remove LIMIT 1, which fixes the plan for Jeff's test >> scenario and some smaller and larger examples I tried.

Re: neqjoinsel versus "refresh materialized view concurrently"

2018-03-13 Thread Jeff Janes
On Tue, Mar 13, 2018 at 4:57 PM, Thomas Munro wrote: > On Wed, Mar 14, 2018 at 12:29 PM, Tom Lane wrote: > > Thomas Munro writes: > >> There is a fundamental and complicated estimation problem lurking here > >>

Re: neqjoinsel versus "refresh materialized view concurrently"

2018-03-13 Thread Thomas Munro
On Wed, Mar 14, 2018 at 12:29 PM, Tom Lane wrote: > Thomas Munro writes: >> There is a fundamental and complicated estimation problem lurking here >> of course and I'm not sure what to think about that yet. Maybe there >> is a very simple fix

Re: neqjoinsel versus "refresh materialized view concurrently"

2018-03-13 Thread Tom Lane
Thomas Munro writes: > This looks like an invisible correlation problem. Yeah --- the planner has no idea that the join rows satisfying newdata.* *= newdata2.* are likely to be exactly the ones not satisfying newdata.ctid <> newdata2.ctid. It's very accidental

Re: neqjoinsel versus "refresh materialized view concurrently"

2018-03-13 Thread Thomas Munro
On Wed, Mar 14, 2018 at 11:34 AM, Thomas Munro wrote: > LOG: duration: 26101.612 ms plan: > Query Text: SELECT newdata FROM pg_temp_3.pg_temp_16452 newdata WHERE > newdata IS NOT NULL AND EXISTS (SELECT * FROM pg_temp_3.pg_temp_16452 > newdata2 WHERE newdata2 IS

Re: neqjoinsel versus "refresh materialized view concurrently"

2018-03-13 Thread Thomas Munro
On Wed, Mar 14, 2018 at 8:07 AM, Jeff Janes wrote: > The following commit has caused a devastating performance regression > in concurrent refresh of MV: > > commit 7ca25b7de6aefa5537e0dbe56541bc41c0464f97 > Author: Tom Lane > Date: Wed Nov 29 22:00:29

neqjoinsel versus "refresh materialized view concurrently"

2018-03-13 Thread Jeff Janes
The following commit has caused a devastating performance regression in concurrent refresh of MV: commit 7ca25b7de6aefa5537e0dbe56541bc41c0464f97 Author: Tom Lane Date: Wed Nov 29 22:00:29 2017 -0500 Fix neqjoinsel's behavior for semi/anti join cases. The below