How to share the result data of separated plan - Mailing list pgsql-hackers

From Hitoshi Harada
Subject How to share the result data of separated plan
Date
Msg-id AANLkTi=pfUFoA8dHa2cTmq59vnYCd3RPPE_E9x3sOD-6@mail.gmail.com
Whole thread Raw
Responses Re: How to share the result data of separated plan
List pgsql-hackers
While designing writeable CTEs, I found it needs a new infrastructure
that helps to share the result data among separated PlannedStmt.

Up to now, in a portal we only have multiple plans that share nothing
but relations, then executor starts, runs and ends separately.
Writeable CTEs need the result tuplestore of separated plans that were
executed before main query . In a prototype Marko and I made adds a
List of DerivedTable to Portal,  which holds each tuplestore and
tupledesc set and the previously-executed result of the child plans
are stored in these structure.

Here we have some problems:

- the process to make tuplestore receiver, to set it to executor of
child plans, to run it and to update snapshot is very common but very
local in pquery.c. We need similar copy in explain.c and functions.c
- we are not sure where to save this List of DerivedTable. In pquery.c
it is Portal, and we can see it in ExplainState in explan.c. But very
messy...
- Wait, isn't there any problem to share data without relations among
separated plans?

The most conservative way to solve it is to make it by rewriter not by
planner to separated executions with temporary tables instead of
tuplestore. In this way we don't do so much in planner and writeable
CTEs is only a rewrite process. The first reason why we are hacking
the planner not rewriter is that the main purpose of writeable CTEs is
the performance gained by using tuplestore instead of temporary
tables. In the second place, we will be able to extend it to combine
RECURSIVE queries (which may be discarded in the first version for
simplicity).

Please find attached patch (pquery.c only) as a prototype we are
working to see what is done about the problem. PlannedStmt now has
childStmt member to store separated child plans(PlannedStmts) and the
portal calls FillDerivedTables() to execute them and fill tuplestores.
Tuplestores are saved in the portal member ddts. They will be referred
by main query so they are passed to CreateQueryDesc() on the main
query execution.

I'm very sorry the explanation is poor, but if you have some thoughts,
please let us know.

Regards,

--
Hitoshi Harada

Attachment

pgsql-hackers by date:

Previous
From: Hitoshi Harada
Date:
Subject: SQL2011 and writeable CTE
Next
From: Roberto Mello
Date:
Subject: Re: Query Plan Columns