Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY - Mailing list pgsql-bugs

From surya poondla
Subject Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date
Msg-id CAOVWO5rfJGpxRDn496gAHmAxW7L_72==KkOcz0q16YOvqm4-=g@mail.gmail.com
Whole thread
In response to Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY  (Rafia Sabih <rafia.pghackers@gmail.com>)
Responses Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
List pgsql-bugs
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

Regrading the performance, (quoting the output from my previous message) with unique index having NULL values we see that both "REFRESH MATERIALIZED VIEW CONCURRENTLY s;" operations (operation 1 was after CREATE UNIQUE INDEX ON s(x); and operation 2 was after CREATE UNIQUE INDEX ON s(x);) take about the same time. Without the patch, operation 2 was taking around ~11000 ms, due to NULL = NULL comparison checks and this was causing the degradation.
 
Regarding different commits to each issue, I don't have any particular opinion but since both the issues are related to the same function and NULL comparison, I feel we can have a single commit, but open to create 2 commits too.

Regards,
Surya Poondla

 

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.
Next
From: Richard Guo
Date:
Subject: Re: BUG #19412: Wrong query result with not null constraint