On Thu, 19 Sept 2024 at 12:01, Tatsuo Ishii <is...@postgresql.org> wrote:
> > Could you add the two sizes together and take the storage type from
> > the tuplestore with the highest storage size?
>
> I don't think this works because tuplestore_begin/tuplestore_end are
> called while executing the node (ExecRecursiveUnion).
>
> I think the way you are proposing only shows the stats last time when
> those tuplestore are created.
That code could be modified to swap the tuplestores and do a
tuplestore_clear() instead of tuplestore_end() followed by
tuplestore_begin_heap().
It's likely worthwhile from a performance point of view. Here's a
small test as an example:
master:
postgres=# with recursive cte (a) as (select 1 union all select
cte.a+1 from cte where cte.a+1 <= 1000000) select count(*) from cte;
Time: 219.023 ms
Time: 218.828 ms
Time: 219.093 ms
with attached patched:
postgres=# with recursive cte (a) as (select 1 union all select
cte.a+1 from cte where cte.a+1 <= 1000000) select count(*) from cte;
Time: 169.734 ms
Time: 164.841 ms
Time: 169.168 ms
David
diff --git a/src/backend/executor/nodeRecursiveunion.c
b/src/backend/executor/nodeRecursiveunion.c
index c7f8a19fa4..8aa7a78d0c 100644
--- a/src/backend/executor/nodeRecursiveunion.c
+++ b/src/backend/executor/nodeRecursiveunion.c
@@ -115,19 +115,26 @@ ExecRecursiveUnion(PlanState *pstate)
slot = ExecProcNode(innerPlan);
if (TupIsNull(slot))
{
+ Tuplestorestate *swaptemp;
+
/* Done if there's nothing in the intermediate table */
if (node->intermediate_empty)
break;
- /* done with old working table ... */
- tuplestore_end(node->working_table);
+ /*
+ * Now we let the intermediate table become the work
table. We
+ * need a fresh intermediate table, so delete the
tuples from
+ * the current working table and use that as the new
intermediate
+ * table. This saves a round of free/malloc from
creating a new
+ * tuple store.
+ */
+ tuplestore_clear(node->working_table);
- /* intermediate table becomes working table */
+ swaptemp = node->working_table;
node->working_table = node->intermediate_table;
+ node->intermediate_table = swaptemp;
- /* create new empty intermediate table */
- node->intermediate_table = tuplestore_begin_heap(false,
false,
-
work_mem);
+ /* mark the intermediate table as empty */
node->intermediate_empty = true;
/* reset the recursive term */
