First, a few things,
UNION [DISTINCT] is the default. This means all of those functions have to be executed and buffered so it knows it is not adding duplicate rows.
UNION ALL does not have these requirements.
UNION ALL return the first row before
function_name3 is completed? I'm not sure. I would guess so, but I think it would depend on buffering, libraries, and other implementations details. But, moreover, I think you're doing this wrong. This is a horrible abomination and a contradiction of logic..
To avoid multiple resquest from the frontend to the server I has grouped all functions in a single funtion like:
This is not what
UNION does. It doesn't pipeline a request. It joins a result set. You do it when it makes sense for your data. You're taking pre-optimizing to the extreme. This just slows down the query planner, and forces you to front-end parse the query.
Unless it makes sense with the data to
UNION, all of these requests should go separately to the server. If you're interfacing with any reasonably library, this should not be an issue. If you're using a library or language that has async/IO, you should use the async IO library for PostgreSQL.
Pg 9.6 Parallel Functions
You can always try to use
PARALLEL functions in your
UNION I'm not sure they've added the optimization here but you can try it.
PARALLEL UNSAFE indicates that the function can't be executed in parallel mode and the presence of such a function in an SQL statement forces a serial execution plan. This is the default. PARALLEL RESTRICTED indicates that the function can be executed in parallel mode, but the execution is restricted to parallel group leader. PARALLEL SAFE indicates that the function is safe to run in parallel mode without restriction.
Functions should be labeled parallel unsafe if they modify any database state, or if they make changes to the transaction such as using sub-transactions, or if they access sequences or attempt to make persistent changes to settings (e.g. setval). They should be labeled as parallel restricted if they access temporary tables, client connection state, cursors, prepared statements, or miscellaneous backend-local state which the system cannot synchronize in parallel mode (e.g. setseed cannot be executed other than by the group leader because a change made by another process would not be reflected in the leader). In general, if a function is labeled as being safe when it is restricted or unsafe, or if it is labeled as being restricted when it is in fact unsafe, it may throw errors or produce wrong answers when used in a parallel query. C-language functions could in theory exhibit totally undefined behavior if mislabeled, since there is no way for the system to protect itself against arbitrary C code, but in most likely cases the result will be no worse than for any other function. If in doubt, functions should be labeled as UNSAFE, which is the default.