Date: Tue, 24 Feb 2026 20:44:17 +0900 (JST)
Message-Id: <20260224.204417.914253025244188300.ishii@postgresql.org>
To: assam258@gmail.com
Cc: vik@postgresfriends.org, er@xs4all.nl, jacob.champion@enterprisedb.com,
 david.g.johnston@gmail.com, peter@eisentraut.org,
 pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: <20260224.140927.1828965853586507533.ishii@postgresql.org>
References: <20260224.115625.1966558814200895991.ishii@postgresql.org>
	<CAAAe_zAc4CQDMP5WVuNZq5q6A_CymN5xwrva38df3okrtYdYsw@mail.gmail.com>
	<20260224.140927.1828965853586507533.ishii@postgresql.org>
Mime-Version: 1.0
Content-Type: Multipart/Mixed;
 boundary="--Next_Part(Tue_Feb_24_20_44_17_2026_043)--"
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20260224.204417.914253025244188300.ishii%40postgresql.org>
Precedence: bulk

----Next_Part(Tue_Feb_24_20_44_17_2026_043)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi Henson,

Currently we do not account the cost of RPR while planning.  Attached
is the first attempt to try to estimate the RPR costs. The cost model
is very simple:

expression cost per PATTERN variable * number of input tuples

Any idea to make this estimation better?

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

----Next_Part(Tue_Feb_24_20_44_17_2026_043)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="cost_rpr.txt"

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 89ca4e08bf1..5a221214b21 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -103,6 +103,7 @@
 #include "optimizer/placeholder.h"
 #include "optimizer/plancat.h"
 #include "optimizer/restrictinfo.h"
+#include "optimizer/rpr.h"
 #include "parser/parsetree.h"
 #include "utils/lsyscache.h"
 #include "utils/selfuncs.h"
@@ -3227,12 +3228,16 @@ cost_windowagg(Path *path, PlannerInfo *root,
 	 * many rows the window function will fetch, it's hard to do better.  In
 	 * any case, it's a good estimate for all the built-in window functions,
 	 * so we'll just do this for now.
+	 *
+	 * Moreover, if DEFINE/PATTERN clause exists, we charge their expressions
+	 * per tuple.
 	 */
 	foreach(lc, windowFuncs)
 	{
 		WindowFunc *wfunc = lfirst_node(WindowFunc, lc);
 		Cost		wfunccost;
 		QualCost	argcosts;
+		QualCost	defcosts;
 
 		argcosts.startup = argcosts.per_tuple = 0;
 		add_function_cost(root, wfunc->winfnoid, (Node *) wfunc,
@@ -3245,6 +3250,37 @@ cost_windowagg(Path *path, PlannerInfo *root,
 		startup_cost += argcosts.startup;
 		wfunccost += argcosts.per_tuple;
 
+		/* also add DEFINE clause expressions' cost to per-input-row costs */
+		if (winclause->rpPattern)
+		{
+			List	   *pattern_vars;	/* list of pattern variable names */
+			ListCell   *lc2;
+
+			pattern_vars = collectPatternVariables(winclause->rpPattern);
+
+			/* iterate according to the pattern variable */
+			foreach(lc2, pattern_vars)
+			{
+				char	   *ptname = strVal((char *) lfirst(lc2));
+
+				/* iterate according to the DEFINE clause */
+				foreach_node(TargetEntry, def, winclause->defineClause)
+				{
+					if (!strcmp(ptname, def->resname))
+					{
+						/*
+						 * varname found. Add DEFINE clause expressions' cost
+						 * to per-input-row costs.
+						 */
+						cost_qual_eval_node(&defcosts, (Node *) def->expr, root);
+						startup_cost += defcosts.startup;
+						wfunccost += defcosts.per_tuple;
+					}
+				}
+			}
+			list_free_deep(pattern_vars);
+		}
+
 		/*
 		 * Add the filter's cost to per-input-row costs.  XXX We should reduce
 		 * input expression costs according to filter selectivity.

----Next_Part(Tue_Feb_24_20_44_17_2026_043)----