Re: neqjoinsel versus "refresh materialized view concurrently"

On Wed, Mar 14, 2018 at 2:56 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Tue, Mar 13, 2018 at 4:57 PM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> 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. The query is
>> already executed with SPI_execute(..., 1) so it'll give up after one
>> row anyway. The regression test includes a case that causes a row to
>> be produced here and that's passing ('ERROR: new data for
>> materialized view "mvtest_mv" contains duplicate rows without any null
>> columns').
>
> 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 the regression tests becomes "noticeably slower", but that is
> pretty vague and not user friendly to formally pass and just hope it is slow
> enough for someone to investigate.

I can't think of a good way. I guess it can still pick a nested loop
if it thinks there'll only be a couple of loops. This patch tells it
to pay attention to the total cost, not the startup cost, so as soon
as it thinks there is more than a hand full of rows the quadratic cost
will exceed the sort/merge's logarithmic cost.

Since I've had hash joins on the mind recently I couldn't help
noticing that you can't get a hash join out of this query's "record
image" based join qual (or even a regular row-based =). Were these
operators rigged up specifically to allow merge joins for this very
matview code? Just for fun, I tried a quick and dirty hack to get
past that by naming the columns explicitly in the query instead.
Maybe that's unfair for a single-column tabe or doesn't have the right
semantics, I'm not sure... But here are the rough times from my
laptop with a 5 million row version of your test:

patched just to remove LIMIT 1: 85s, of which 61s in the dup-check query (sort)
hacked to name columns: 38s, of which 14s in the dup-check query (hash)
hacked to name columns, work_mem=1GB: 31s, of which 4s in the
dup-check query (hash)

The reason I thought about that is because Parallel Hash is really
good at scaling big stupid self-joins (though for that we'd need
UNLOGGED instead of TEMP tables, which I didn't have time to try out
today).

--
Thomas Munro
http://www.enterprisedb.com