PostgreSQL for the Oracle DBA, Part 4: Query Tuning and the PostgreSQL Planner

As an Oracle DBA venturing into the world of PostgreSQL, one of the most important areas to get comfortable with is performance tuning. While Oracle’s Cost-Based Optimizer (CBO) is a well-known powerhouse that many DBAs have learned to both respect and wrestle with, PostgreSQL offers its own sophisticated query planner that behaves differently.  Understanding these differences is key to becoming proficient with PostgreSQL as we step into tuning.

In this post, we’ll explore:

• The role of pg_stat_statements in query tuning

• How PostgreSQL’s planner works

• And how tuning in PostgreSQL compares to the Oracle CBO experience

pg_stat_statements the Work Horse

Oracle DBAs are used to tools like AWR reports, v$sql views, and monitoring reports to identify high-cost or frequently executed queries. In PostgreSQL, pg_stat_statements is your closest equivalent and due to Oracle’s extensive investment in performance data, can be a bit frustrating for those skilled in Oracle’s robust tools.

pg_stat_statements is an extension that collects execution statistics of all SQL statements executed by a server. When enabled, it provides normalized query fingerprints along with detailed execution metrics, including total time, number of calls, average time, and more.

To use pg_stat_statements, as with other extensions, it must be enabled before it can be used.

Enabling pg_stat_statements:

Add the following to the postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Followed by a restart of PostgreSQL and then execute:

CREATE EXTENSION pg_stat_statements;

You can query pg_stats_statements with the following:

SELECT query,
       calls,
       total_time,
       rows,
       mean_time,
       stddev_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

This gives you a view of your top resource-consuming queries and is incredibly helpful for performance triage and ongoing tuning efforts.

Postgres Query Planner is All About Row Estimates, Cost, and Execution

PostgreSQL uses a cost-based planner that resembles the Oracle’s CBO in concept but there are critical differences in execution and transparency.

Where Oracle uses highly curated statistics collected via DBMS_STATS and a deeply integrated optimizer, PostgreSQL uses a more lightweight sampling and assumes uniform distribution in most cases.

Some of the data provided by PostgreSQL’s planner includes:

• Cost Units: PostgreSQL’s “cost” is an abstract unit, not tied to time or I/O directly. The cost model is affected by parameters like random_page_cost and cpu_tuple_cost.
• Row Estimates: The planner uses table and column statistics to estimate row counts and selectivity. Errors in these estimates can lead to suboptimal plans, especially with skewed data.
• Plan Types: PostgreSQL’s plan can include sequential scans, index scans, bitmap index scans, nested loop joins, hash joins, and merge joins.

You can view a plan using the following addition to your SQL statement.  This creates an estimate based off the current values in the database:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

You’ll get a full trace of actual vs. estimated rows, execution times, and join types used, which is very useful for debugging plans.

A Few Tuning Differences in PostgreSQL vs. Oracle

Area	Oracle CBO	PostgreSQL Planner
Stats Collection	Manual or scheduled via DBMS_STATS	Automatic via autovacuum, or manually via ANALYZE
Histograms	Supported, important for skewed data	Limited support (e.g., CREATE STATISTICS for cross-column stats)
Hints	SQL hints and plan directives available	No hinting; planner decisions can’t be overridden directly (extensions like pg_hint_plan exist, but are rarely used)
Parallelism	Controlled via degree settings; auto and manual options	Parallelism introduced in later versions, less granular control
Execution Plan Reuse	Cursors and stored outlines for stability	Each statement is planned at execution (prepared statements offer reuse)
Tools	AWR, ADDM, SQL Monitor	pg_stat_statements, auto_explain, EXPLAIN (ANALYZE, BUFFERS), PGNow or PG Monitor

Best Practices for Query Tuning in PostgreSQL

1. Always Check Row Estimates: A mismatch between estimated and actual rows is a red flag that statistics may need updating.
2. Use pg_stat_statements Regularly: Monitor for long-running or frequently executed queries. This view is essential for tracking query performance trends.
3. Keep Autovacuum Healthy: It maintains table stats and helps avoid bloat. Adjust thresholds if you have high-write workloads.
4. Index Wisely: PostgreSQL supports B-tree, GIN, GiST, and more. Know when and where to apply each.
5. Tune Planner Parameters: Parameters like random_page_cost and work_mem can drastically change plan behavior.
6. Leverage Extensions: Tools like auto_explain can log slow queries with their plan, even without EXPLAIN manually.

Summary

For Oracle DBAs who need to also manage Postgres, the most important shift is in mindset. Postgres provides deep visibility and flexibility, but with fewer constraints and less directive control over execution. You don’t hint the planner, but you help it with better statistics and smarter indexing.

You can embrace pg_stat_statements as your new v$sql, learn to interpret EXPLAIN ANALYZE like you would create an explain plan in Oracle, and recognize that while PostgreSQL’s planner may not be as feature rich as Oracle’s CBO, it’s transparent, fast, and steadily improving with the help of community investment.

As you build your PostgreSQL skillset, you’ll find that most of your Oracle tuning instincts remain valuable and should be relied on deeply.  You just use it with different tools and a slightly different playbook.