| Table of Contents |
|---|
Identification of the issue
Kiwi queries originating from LDP requests are run as prepared queries. In PSQL 9.1 and before, these queries are executed using a generic query plan, allowing for arbitrary entries for each variable (including pattern matching, etc...) in the executed query.
...
Postgresql 9.2 allows custom query plans for prepared queries, and should alleviate this problem. See: http://blog.endpoint.com/2014/04/custom-plans-prepared-statements-in.html
Post-upgrade notes
We have upgraded PostgreSQL from 9.1 to 9.4.
Please see Creating a differently-ordered index on `triples` concerning a fix for slow queries that we still saw after the upgrade.
We are now performing the same activities as before, but without logging any slow (> 200ms) queries.
We've been investigating how prepared statements work in the context of our application, and have noted that prepared statements may not be advantageous for the queries that run during PUT requests to "select id from triples ...". See our Gist. See the PostgreSQL reference page on PREPARE, which states, "The performance difference will be particularly significant if the statements are complex to plan or rewrite, for example, if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable."
We are also observing that, after a client (e.g. database session) runs the same prepared statement (with different parameters) five times, planning switches over to the backend server, which proceeds to start using a generic query plan again.