Postgresql

From a recent discussion on the Postgres performance mailing list, I learned that the configuration of the SQL client has an influence on the execution plan chosen by Postgres.

Sometimes it’s necessary to run dynamic SQL, but it’s not always possible (or sensible) to write a stored procedure just for that.

Postgres and Oracle both support functions that can run queries using dynamic SQL and return the result of those queries as XML.

One type of problems is inherently complicated with SQL: turning rows into columns. This is very often referred to as a “crosstab” or “pivot”.

Every now and then I see queries that use GROUP BY without any aggregates which is the same as using DISTINCT instead.

Sometimes it’s necessary to normalize de-normalized tables - the opposite of a “crosstab” or “pivot” operation. Postgres does not support an UNPIVOT operator like Oracle or SQL Server, but simulating it, is very simple.

Something that users coming from other database systems easily overlook is, that Postgres supports different languages for writing stored functions (and procedures). Choosing the right language can influence the performance of your functions.