neqjoinsel versus "refresh materialized view concurrently"
Jeff Janes <jeff.janes@gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2018-03-13T19:07:59Z
Lists: pgsql-hackers
The following commit has caused a devastating performance regression
in concurrent refresh of MV:
commit 7ca25b7de6aefa5537e0dbe56541bc41c0464f97
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed Nov 29 22:00:29 2017 -0500
Fix neqjoinsel's behavior for semi/anti join cases.
The below reproduction goes from taking about 1 second to refresh, to
taking an amount of time I don't have the patience to measure.
drop table foobar2 cascade;
create table foobar2 as select * from generate_series(1,200000);
create materialized view foobar3 as select * from foobar2;
create unique index on foobar3 (generate_series );
analyze foobar3;
refresh materialized view CONCURRENTLY foobar3 ;
When I interrupt the refresh, I get a message including this line:
CONTEXT: SQL statement "SELECT newdata FROM pg_temp_3.pg_temp_16420
newdata WHERE newdata IS NOT NULL AND EXISTS (SELECT * FROM
pg_temp_3.pg_temp_16420 newdata2 WHERE newdata2 IS NOT NULL AND newdata2
OPERATOR(pg_catalog.*=) newdata AND newdata2.ctid OPERATOR(pg_catalog.<>)
newdata.ctid) LIMIT 1"
So I makes sense that the commit in question could have caused a change in
the execution plan. Because these are temp tables, I can't easily get my
hands on them to investigate further.
Cheers,
Jeff
Commits
-
Fix performance hazard in REFRESH MATERIALIZED VIEW CONCURRENTLY.
- 1568156d8fe1 10.4 landed
- 6fbd5cce22eb 11.0 landed
- ebcf34d4628e 9.6.9 landed
- c553e4a50743 9.5.13 landed
- b6ba94ec45bb 9.4.18 landed
-
Fix neqjoinsel's behavior for semi/anti join cases.
- 7ca25b7de6ae 11.0 cited