Performance Tuning Large PL/PGSQL Stored Procedure

I would greatly appreciate any advice anyone could give me in terms of
performance tuning a large PL/PGSQL stored procedure. First, I should point
out that I have read a considerable amount of information in the online
PostgreSQL documentation and on Google about optimizing SQL queries and
PostgreSQL. I am looking for any additional insights that my research may
have overlooked. So, let me explain a little about how this stored procedure
is constructed.

The stored procedure is written in PL/PGSQL and is 3,000+ lines long. It
works with around 60 tables and a dozen or so complex types that are defined
in an additional 2,000 lines of SQL.

The procedure takes individual arguments of various types as input
parameters and returns a single row result of a complex type.

The complex type contains roughly 25 fields, mostly text, plus another 10
REFCURSORs.

The application that calls the stored procedure was also written by me in
C++ and uses asynchronous libpq API commands to execute a single SQL
transaction which calls the stored procedure and also performs a FETCH ALL
on all open cursors. It then returns all results into various structures.
All rows of all cursors that are open are always used for every call to the
stored procedure.

The stored procedure implements various logic which determines which tables
in the database to query and how to filter the results from those queries to
return only the relevant information needed by the C++ application.

Currently, in terms of optimization, I have taken the following approaches
based on the following reasoning:

1. For all queries whose results need to return to the C++ application, I
utilize cursors so that all results can be readied and generated by the
stored procedure with just one call to the PostgreSQL backend. I accomplish
this using asynchronous libpq API calls to issue a single transaction to the
server. The first command after the BEGIN is a SELECT * FROM
MyStoredProc(blah), which is then followed by FETCH ALL commands for each
cursor that the stored procedure leaves open. I then follow up with multiple
API calls to return the results and retrieve the rows from those results.
This minimizes the amount of back-and-forth between my C++ application and
the database backend.

1a. Incidentally, I am also using cursors for most queries inside the stored
procedure that do not return results to the C++ application. I am unsure
whether this incurs a performance penalty compared to doing, for example, a
SELECT ... INTO (var1, var2, ...) within the body of the stored procedure.
Instead of using SELECT ... INTO, I am using OPEN cursor_name; FETCH
cursor_name INTO (var1, var2).

2. I have built indexes on all columns that are used in where clauses and
joins.

3. I use lots of joins to pull in data from various tables (there are around
60 tables that are queried with each call to the stored procedure).

4. When performing joins, the first table listed is the one that returns the
most context-specific results, which always also means that it has the
most-specific and fewest number of relevant rows. I then join them in order
of least number of result rows with all inner joins preceding left outer
joins.

5. Instead of using UNIONs and EXCEPT clauses, I use multiple WITH clauses
to define several different query-specific views. I order them such that I
can join additional tables in later WITH clauses to the views created
previously in a way that minimizes the number of rows involved in the JOIN
operations while still providing provably accurate result sets. The EXCEPT
clauses are then replaced by also defining one view which contains a set of
IDs that I want filtered from the final result set and using a WHERE id NOT
IN (SELECT id FROM filtered_view). Typically, this approach leaves me with
just one UNION of two previously defined views (the union is required
because it is returning results from different tables with different
columns), which is then aliased and joined to additional tables. This allows
all of the the JOINS and the sole remaining UNION to be applied just once
each in calculation of the final result set. As an example, two of the
queries I replaced with this approach utilized four UNIONs followed by two
EXCEPT clauses, and each of those utilized as many as 8 JOINs in building
their result sets. In one case the query dropped from 173 "explain analyze"
lines to 71 "explain analyze" lines and dropped from 1.2ms execution time to
0.49ms execution time. The other query started at 136 "explain analyze"
lines and dropped to 66 "explain analyze" lines. It's execution time dropped
from 1.6ms to 0.66ms. This is due to the fact that each WITH clause (and the
JOINS/UNIONS contained in them) are executed just once for each query and
can be used multiple times later. In addition, filters can be applied to the
individual result sets for each WITH clause which reduces the number of rows
being worked on during later JOIN and filtering operations.

6. I specify individual columns that are returned for nearly every query
utilized in the stored procedure.

7. When I have a query I need to execute whose results will be used in
several other queries, I currently open the cursor for that query using the
FOR ... LOOP construct to retrieve all records in the result set and build a
result array using the array_append() method. I then do an unnest(my_array)
AS blah inside the other queries where I need to use the results so that
they do not need to be re-computed for each query. I am unsure about how
efficient this method is, and I was wondering if there is some way to create
a view inside a stored procedure that could be used instead. In each of the
cases where I do this, the results from the set must be returned via an open
cursor to my C++ application as it also requires the results from these
particular queries.

Some things to note:

For most of the joins, they simply join on foreign key IDs and no additional
filtering criteria are used on their information. Only a handful of the
joined tables bring in additional criteria by which to filter the result
set.

The approach used in 7 with cursors and building a result array which is
then unnested has me worried in terms of performance. It seems to me there
should be some better way to accomplish the same thing.

The stored procedure does not perform updates or inserts, only selects.

Anyway, if anyone has some insights into performance tweaks or new
approaches I might try that may lead to enhanced performance, I would
greatly appreciate hearing about them. I am not completely dissatisfied with
the performance of the stored procedure, but this is going to be used in a
very high volume environment (hundreds or possibly even thousands of calls
to this stored procedure every second). The more performant it is, the less
hardware I need to deploy. It currently takes about 45ms to execute the
query and retrieve all of the results into the C++ application. Query
execution time takes up about 16ms of that 45ms. This is on a 3-year old
Core 2 Duo, so it's not exactly top-of-the-line hardware.

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be a
crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not
live to eat.) ~Marcus Tullius Cicero