Optimizing PostgreSQL queries
Lain mentioned that the recent changes to optimize our database have caused an unfortunate side effect in query performance. This is likely due to the way the prepared statements work in PostgreSQL [1] and the joins we have to do between tables:
Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the DEALLOCATE command.
also
Prepared statements can use generic plans rather than re-planning with each set of supplied EXECUTE values. This occurs immediately for prepared statements with no parameters; otherwise it occurs only after five or more executions produce plans whose estimated cost average (including planning overhead) is more expensive than the generic plan cost estimate. Once a generic plan is chosen, it is used for the remaining lifetime of the prepared statement. Using EXECUTE values which are rare in columns with many duplicates can generate custom plans that are so much cheaper than the generic plan, even after adding planning overhead, that the generic plan might never be used.
Ecto does not issue DEALLOCATE, but the pin operator [2] will cause Ecto to generate parameterized queries:
The pin operator instructs the query builder to use parameterised SQL queries protecting against SQL injection.
Are there queries in Pleroma that are not parameterized ?
Are there any expensive queries that would benefit from being a stored procedure so the query planner won't be skipped past the 5th execution?
[1] https://www.postgresql.org/docs/current/sql-prepare.html