Highlights from the PostgreSQL 9.2 beta

Highlights from the PostgreSQL 9.2 beta

Posted May 16, 2012 2:15 UTC (Wed) by ringerc (subscriber, #3071)
Parent article: Highlights from the PostgreSQL 9.2 beta

A huge and important change isn't mentioned in the article, but will make a massive difference to lots of Pg users, eliminate a really common mailing list FAQ, and greatly improve performance in several very common use cases. From the relnotes:

Improve the ability of the planner to choose parameterized plans (Tom Lane)

A prepared statement is now parsed, analyzed, and rewritten, but not necessarily planned. When the prepared plan is executed with parameters, the planner might replan it for every constant, or it might execute a generic plan if its cost is close to that of a constant-specific plan. CLARIFY"

If you're wondering why this is important: Say you have a table with a very common value and an uncommon value. You run queries that filter on the uncommon value, relying on an index to avoid scanning the rest of the table. Everything goes well until you convert it to a parameterised prepared statement - perhaps even one created automatically behind the scenes by PgJDBC or similar. Suddenly queries take hundreds of times as long and you don't know why. Eventually, using auto_explain, or an explicit PREPARE followed by ANALYZE EXECUTE, you find out that the prepared statement version of your query isn't using the index. WTF?

Pg was planning prepared statments at PREPARE time. If the value for a parameter wasn't known, it couldn't use the stats for the column to find out if it was a very common value or an uncommon one. It'd often land up choosing a conservative approach that left it using a seqscan instead of an index scan, especially if the server was tuned with the default weights for seq_page_cost and random_page_cost. Much wailing, gnashing of teeth resulted, along with aggressively unrealistic random_page_cost settings and pleading for query hints.

Now, all that should be gone! This is awesome, and a huge usability/performance win. Thanks so much Tom.

---

to post comments