PostgreSQL provides the means to figure out why a certain query is slow. Behind the scenes, PostgreSQL analyzes tables, collects statistics from them, and builds histograms using autovacuuming. Autovacuuming, in general, is used to recover disk space, update table statistics, and perform other maintenance tasks, such as preventing transaction ID wraparound. Table statistics allow PostgreSQL to pick up an execution plan at the least cost. The least cost is calculated by taking into account the IO and, naturally, the CPU cost. Also, PostgreSQL enables users to see the generated execution plan by providing the EXPLAIN command.

For beginners, it is extremely useful to write the same query in different ways and compare the results. For example, in some cases, the NOT IN construct can be converted to LEFT JOIN or NOT EXIST. Also, the IN construct can be rewritten using INNER JOIN as well as EXISTS. Writing the query in several ways teaches the developer when to use or avoid a certain construct and what the conditions that favor a certain construct are. In general, the NOT IN construct can sometimes cause performance issues because the planner cannot use indexes to evaluate the query.

Another important issue is to keep tracking new SQL commands and features. The PostgreSQL development community is very active, and their contributions are often targeted toward solving common issues. For example, the LATERAL JOIN construct, which was introduced in PostgreSQL 9.3, can be used to optimize certain GROUP BY and LIMIT scenarios.