On Wed, Mar 14, 2018 at 2:56 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Tue, Mar 13, 2018 at 4:57 PM, Thomas Munro > <thomas.mu...@enterprisedb.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