Schedule - PGConf.EU 2011

Debugging complex SQL queries with writable CTEs

Date: 2011-10-20
Time: 13:40–14:30
Room: Amsterdam 3
Level: Intermediate

There are many reasons to prefer declarative languages such as SQL instead of imperative ones; for instance, the machine can make optimisations that you couldn't possibly (or just didn't) see. Furthermore, future versions of the machine can become more clever and optimise better, while your imperative code will stand still.

On the other hand, the more you delegate the machine and the more difficult it becomes to fix problems when they happen. For instance, this is the case when you have a very complex query with lots of subqueries, and even using EXPLAIN output you still can't understand why the output differs from what you expected.

In this talk we show how CTE (a.k.a. WITH queries), and more precisely the writable ones introduced in PostgreSQL 9.1, can provide a valuable debugging and tracing tool. We will provide concrete examples and try to point out pitfalls.

Speaker

Gianni Ciolli

Platinum Sponsor

Gold Sponsors

Silver Sponsors