From 98eac30e75e806e4f8daafdf2a041abf1fc678b7 Mon Sep 17 00:00:00 2001 From: Zhang Mingli Date: Sun, 1 Oct 2023 22:24:49 +0800 Subject: [PATCH] Skip Orderby clause execution for Materialized Views MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit The order is not guaranteed even if materialized view's sql has order by clause. Materialized view’s order by clause could be skipped when execution if order by clause is on the top query level and there is no real limit clause. This will speed up for REFRESH and CREATE AS on materialized views. Authored-by: Zhang Mingli avamingli@gmail.com --- src/backend/commands/createas.c | 4 ++++ src/backend/commands/matview.c | 5 +++++ 2 files changed, 9 insertions(+) diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index e91920ca14..35846b4ac6 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -50,6 +50,7 @@ #include "utils/rel.h" #include "utils/rls.h" #include "utils/snapmgr.h" +#include "optimizer/planner.h" typedef struct { @@ -141,6 +142,9 @@ create_ctas_internal(List *attrList, IntoClause *into) Query *query = (Query *) copyObject(into->viewQuery); StoreViewQuery(intoRelationAddr.objectId, query, false); + /* No need to sort for materialized view */ + if(!limit_needed(query)) + query->sortClause = NULL; CommandCounterIncrement(); } diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index ac2e74fa3f..3b9ebc0077 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -45,6 +45,7 @@ #include "utils/rel.h" #include "utils/snapmgr.h" #include "utils/syscache.h" +#include "optimizer/planner.h" typedef struct @@ -392,6 +393,10 @@ refresh_matview_datafill(DestReceiver *dest, Query *query, elog(ERROR, "unexpected rewrite result for REFRESH MATERIALIZED VIEW"); query = (Query *) linitial(rewritten); + /* No need to sort for materialized view */ + if(!limit_needed(query)) + query->sortClause = NULL; + /* Check for user-requested abort. */ CHECK_FOR_INTERRUPTS(); -- 2.36.1