The difference is negligible much of the time. You'll see significant benefits if:
- There are lots of joins or lots of predicates; or
- The query contains long
IN lists or similar large data elements
In general, unless it's a "hot" code path where you really hammer a particular query I wouldn't make much effort optimising to focus on re-usable prepared statements.
(You should still parameterise your SQL rather than substituting parameters directly into the text, and people often confuse that with prepraed statements, but you seem to understand the difference.)
The next to last major step was to find out that it is, unfortunately for MySQL users, only MySQL that joins slowly, so these write intensive tables were all given unique integer identifiers to join upon. That took off 1/3 of the remaining average time consumption reduction.
The last step was taking CTE optimization fencing into account which took off the last 2/3 of reduction.
In the case of the above set of queries, the average time consumed is now between 1 and 2ms on an i7 laptop tuned for SSDs even though I'm using the included hard drive. On a server with a single SSD, the time consumed averages less than 1ms.
To get the last performance boost, almost all queries were condensed, so for example if a table needed to be updated, it's best to do it in one query no matter how strange or performance reducing it might appear.
Reading to assemble relevant primary key values required more testing. If it was a combination of RETURNINGs, it was best to allow each sub-statement to recalculate it rather than depend upon another sub-statement to do the calculation. If the read had to go to disk, even if it was small, it was best to do that once and reference it across the multiple writing queries.
Copying was another grey area. If a table required inserts, updates, and deletes based upon data from another table, it was slower to pre-select the data. It was faster to simply reference the primary key data.
In general, aside from reads that go to disk to assemble primary key values to reference, it is best to compress a CTE as much as possible though it may appear strange to keep the chains as short and narrow as possible.
Multi-prepared statement transactions that previously consumed 10s of ms in my application now consume max 5ms.
This approach should probably be limited to small amounts of redundant data.
Best Answer
If I understand you correctly, you want to use functions.
Those are stored (with their code), their execution plans (may be) cached and so on. Plan caching is not so simple as it first sounds. Read https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING and the linked page about
PREPARE.For the additional questions from your comments, PostgreSQL functions don't necessarily protect you from SQL injection. Building dynamic SQL statements the wrong way (using concatenation without proper quoting) can still open the possibility for such attacks.