I’ve experimented more since writing the initial blog in an attempt to find a better solution. PostgreSQL does have a “prepareThreshold” parameter on connections and individual statements that provides control over whether named or unnamed statements are created on the server. Named statements create a query plan at the time of creation whereas unnamed statements create a query plan with knowledge of the bind variables. This functionality would normally solve the problem. Unfortunately in my case I’m also using a result set batch size of 10000 which forces the use of named statements. I have to set the batch size in order to keep memory usage at a minimum, Osmosis is designed to be “streamy” and can handle arbitrary data set sizes.

The other avenue I’ve pursued is using temporary tables. I first create a temporary table containing the id and version of the records I’m interested in (ie. the sub-select portion of the query). I then ANALYZE the table to update its query planner statistics and then select from the nodes table joining to this temporary table instead of using a sub-select. This means the final query doesn’t use bind parameters at all, and it also allows me to re-use the temporary table in subsequent queries. The initial temp table creation query uses bind variables but doesn’t return a result set which means that unnamed server statements are used and bind variables are taken into account. The temp table creation is working well, but for some reason the retrieval query (joining to the temp table) still isn’t producing good query plans in some cases although I’m going to get the DBA to double check my results.

For now I’m using the temporary table approach which avoids bind variable issues but I’m still forcing the query planner to avoid sequential scans, hash joins and merge joins. This appears to be performing very well, but I’ll continue to work on it with the DBA to see if we can get the database to produce sane query plans on its own.

When you tell the database to do a prepared statement, you specifically are instructing it to create the best possible query path for *any* possible value that could be bound into the parameters. For tables and indexes with highly skewed statistics, this often means that the average case query plan is non-performant with the ‘outlier’ case. (Which doesn’t seem like an outlier to you, because you’re providing the values right then and there)

Your solution analysis is 100% correct, although, I would warn against tuning down the overall ability of the database to optimize prepared statements. That might help this case, but the optimizer in modern databases has been tuned well for the general case by many years of hard lessons. Instead, I’d look at your solution 2 - or, do that same type of work in code. It’s probably only a single parameter that’s causing the skew. Instead of binding it using a setParameter(), use a Velocity template or such to ‘SQL Inject’ it in (being very careful if it’s user supplied data).

This puts extra pressure on the number of prepared statements the database has to keep track of, but, if you tune it for only those skewed values, you can get the results you want, while still enjoying the benefits of prepared statements for the rest of your parameters.

As an aside, if you use a java based SQL tool like Aqua Data Studio, you’ll often be able to run the explain query with the parameters as ‘?’ - This should have given you the nasty query plan you eventually saw. Our team has learned the hard way to always run an explain in that mode against production statistics. It is the only way to truly see what’s going to happen on the database.

Our solution was we cut the histogram on those troublesome columns and stop using “auto sample size” when analyzing our schema.
Cheers

http://www.theserverside.com/tt/articles/article.tss?l=RemoteLazyLoadinginHibernate

The Performance Unit Test concept is interesting to me… but I foresee problems where it is difficult to specify explicit performance criteria for a unit test. Without meaningful and specific pass/fail criteria, a unit test is just a to-do item to check off a list. Often, it can be difficult to get SLAs defined for the end-to-end application/transaction/etc. How did you go about determining SLAs for low-level performance unit tests?

I think it would be more practical and lower effort (cost) to implement performance integration tests, ideally as part of a continuous integration build process.

One thing that l did notice though… the JavaScript libraries were always a little hard to follow. For example, when we added event handlers to our HTML elements, we added methods to a JavaScript file i.e. they were not defined in the markup (HTML). Did you have a solution to this problem?