Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitebcf34d

Browse files
committed
Fix performance hazard in REFRESH MATERIALIZED VIEW CONCURRENTLY.
Jeff Janes discovered that commit7ca25b7 made one of the queries run byREFRESH MATERIALIZED VIEW CONCURRENTLY perform badly. The root cause isbad cardinality estimation for correlated quals, but a principled solutionto that problem is some way off, especially since the planner lacks anystatistics about whole-row variables. Moreover, in non-error cases thisquery produces no rows, meaning it must be run to completion; but use ofLIMIT 1 encourages the planner to pick a fast-start, slow-completion plan,exactly not what we want. Remove the LIMIT clause, and instead rely onthe count parameter we pass to SPI_execute() to prevent excess work if thequery does return some rows.While we've heard no field reports of planner misbehavior with this query,it could be that people are having performance issues that haven't reachedthe level of pain needed to cause a bug report. In any case, that LIMITclause can't possibly do anything helpful with any existing version of theplanner, and it demonstrably can cause bad choices in some cases, soback-patch to 9.4 where the code was introduced.Thomas MunroDiscussion:https://postgr.es/m/CAMkU=1z-JoGymHneGHar1cru4F1XDfHqJDzxP_CtK5cL3DOfmg@mail.gmail.com
1 parent7b544c4 commitebcf34d

File tree

1 file changed

+2
-2
lines changed

1 file changed

+2
-2
lines changed

‎src/backend/commands/matview.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -636,10 +636,10 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
636636
appendStringInfo(&querybuf,
637637
"SELECT newdata FROM %s newdata "
638638
"WHERE newdata IS NOT NULL AND EXISTS "
639-
"(SELECT* FROM %s newdata2 WHERE newdata2 IS NOT NULL "
639+
"(SELECT1 FROM %s newdata2 WHERE newdata2 IS NOT NULL "
640640
"AND newdata2 OPERATOR(pg_catalog.*=) newdata "
641641
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
642-
"newdata.ctid) LIMIT 1",
642+
"newdata.ctid)",
643643
tempname,tempname);
644644
if (SPI_execute(querybuf.data, false,1)!=SPI_OK_SELECT)
645645
elog(ERROR,"SPI_exec failed: %s",querybuf.data);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp