Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

On Thu, 12 Feb 2026 at 18:08, surya poondla <suryapoondla4(at)gmail(dot)com> wrote:

> Hi All,
>
> Right now, I am exploring the issue 2.
>>
>
> I am not sure if anyone else has submitted a patch for issue 2. I don't
> see any updates from the mailing list yet. I guess the mailing list has
> some delays, apologies if my efforts look duplicated to other people's
> efforts.
>
> I have a potential patch for issue 2.
> I was able to reproduce the issue, saw the performance degradation, and,
> with my patch (attached) I see an improvement in the REFRESH MATERIALIZED
> VIEW CONCURRENTLY.
>
> The main crux of issue 2 is: when a materialized view has unique index on
> a nullable column, and when we did REFRESH MATERIALIZED VIEW CONCURRENTLY
> it would include that column in the FULL OUTER
> JOIN condition used to detect changes.
> The nullable column was showing severe performance degradation because
> NULL = NULL comparisons evaluate to NULL, making all rows appear different
> even when unchanged!
>
> The fix I explored is to skip nullable columns when building the FULL
> OUTER JOIN conditions. Only include columns with NOT NULL constraints from
> unique indexes. The record equality operator (*=) is always included and
> handles nullable columns correctly.
>
> Here is the output and performance improvement:
>
> postgres=# \timing on
>
> Timing is on.
>
> postgres=# DROP MATERIALIZED VIEW IF EXISTS s CASCADE;
>
> NOTICE: materialized view "s" does not exist, skipping
>
> DROP MATERIALIZED VIEW
>
> Time: 0.858 ms
>
> postgres=#
>
> postgres=# CREATE MATERIALIZED VIEW s AS SELECT generate_series as x, null
> as y FROM generate_series(1, 1000000);
>
> SELECT 1000000
>
> Time: 1076.254 ms (00:01.076)
>
> postgres=#
>
> postgres=# CREATE UNIQUE INDEX ON s(x);
>
> CREATE INDEX
>
> Time: 375.026 ms
>
> postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY s;
>
> REFRESH MATERIALIZED VIEW
>
> Time: 3807.143 ms (00:03.807)
>
> postgres=# CREATE UNIQUE INDEX ON s(y);
>
> CREATE INDEX
>
> Time: 331.382 ms
>
> postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY s;
>
> REFRESH MATERIALIZED VIEW
>
> Time: 3636.049 ms (00:03.636)
> postgres=#
>
> As we can see the REFRESH MATERIALIZED VIEW CONCURRENTLY now takes 3636.049
> ms
>
> With the current patch for issue 2, there is a trade-off.
> The fix skips nullable columns from the join condition to avoid slowness
> when NULLs exist (9s vs 3s in testing). This may slightly slow down cases
> where nullable columns (unique index) never contain NULLs.
> Users can restore full performance by adding the NOT NULL constraints to
> the column if they know there will never be any nulls on that column.
>
> I would love to hear any feedback on this tradeoff and am happy to
> implement relevant changes.
>
> Note: The attached patch addresses both issue 1, issue 2.
>
> Regards,
> Surya Poondla.
>
> Thanks for working on this.
Firstly, since both are different issues, it makes sense to write patches
for each of them separately.
Secondly, for issue 1 it is important to understand why the code was
explicitly done for null columns, what are the scenarios in which this
modified code could cause issues.
Also, for issue 1, additional test case should be added.

For issue 2, it would be helpful if you may share some performance numbers
to confirm if this solution is only improving the performance and not
causing any regressions.
--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH