Also, I’m aware that there is a limit of 5000 prepared statements. So, when using non-parameterized queries as above, do two identical queries with different values for the where clause use different prepared statements?
EG Would the following 2 queries used as above use the same prepared statement?
select * from default where name = 'fred'; select * from default where name = 'dave';
you can use the simple method as above, but benefiting from prepared statements will only work if the query is identical. So for your where clause in order to get the benefit from prepared statements you need to use a parameterized query (ala name = $1), then it will reuse it properly.
Note that caching and reusing the prepared plan should only shave off a few hundred micros in general, so a SELECT * you probably won’t notice a big difference. Note that we just use LRU on the 5000 statements, so there is no need to do some special stuff on your side here. Only make sure the query is “the same” for the engine by using parameters if the input values differ.