PostgreSQL 10 - single big query in UDFs or multiple smaller ones?

Question

Many times, I'm able to write a PL/PGSQL UDF in two ways:

1. As a big query usually using WITH clauses and UNION (i.e. more declarative but also more complex to write/read)
2. As multiple smaller queries (i.e. more procedural but also easier to write/read)

I'm aware that PG query planner will plan each smaller query in UDF individually, and I'm afraid that might have an impact on overall performance. What do you guys prefer?

Thank you.

Answer 1

If you're able to write it as a single query Q, the best alternative is to avoid writing the UDF in the first place, and embed Q as a subquery in the calling query. That's probably the best if performance is your main concern.

If code reuse, modularity, readability are more important than performance, IMO you can choose option 2.