Unlocking Database Performance: Compare Oracle, MySQL, and PostgreSQL Execution Plans

As a DBA, you may encounter performance bottlenecks and slow queries without knowing how to address them; the key lies in understanding execution plans.

1. Execution Plan Comparison

1.1 Intervention Methods

PostgreSQL can only change the execution plan by analyzing tables; it does not support hints.

Oracle can alter plans via statistics collection and also supports hints to directly influence plan generation.

MySQL supports hint‑like features similar to Oracle, but its optimizer is simpler and less powerful for complex queries.

1.2 Cache Mechanism Differences

Oracle (and SQL Server) automatically caches execution plans, allowing reuse even when the SQL text differs in case.

PostgreSQL does not automatically cache plans for each statement; it parses and optimizes anew each time, though prepared statements and PL/pgSQL functions do cache plans.

1.3 Query Efficiency Characteristics

Oracle excels at large‑scale analytics such as aggregations, sorting, deduplication, and joins.

PostgreSQL shines in single‑row processing, spatial queries, and data conversion, offering many built‑in functions.

MySQL performs well on simple reads and writes but lags behind Oracle for complex queries and big‑data analysis.

2. Viewing Execution Plans

2.1 Oracle

Mnemonic for reading Oracle plans: “rightmost top – same level next – step back layer”. Deeper indentation means earlier execution; when indentation is equal, execution proceeds from top to bottom and right to left.

Common ways to view Oracle execution plans:

2.2 MySQL

Common ways to view MySQL execution plans:

2.3 PostgreSQL

1. Estimate without execution
EXPLAIN SELECT * FROM users LIMIT 10;

2. Execute to get the real plan
EXPLAIN ANALYSE SELECT * FROM users LIMIT 10;

Add BUFFERS to see how much data comes from cache vs. disk
EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM users LIMIT 10 OFFSET 200;

VERBOSE provides extra details
EXPLAIN (ANALYSE,BUFFERS,VERBOSE) SELECT * FROM users LIMIT 10 OFFSET 500;

3. Interpreting Execution Plans

Regardless of the database, focus on these key aspects when reading an execution plan:

Operation type : e.g., Full Table Scan, Index Lookup, etc.

Cost : numeric estimate; lower cost usually means faster execution.

Rows : estimated number of rows returned by each step.

Execution order : the sequence in which operations are performed.

Conclusion

Mastering execution‑plan analysis enables DBAs to quickly pinpoint performance issues, understand optimizer behavior, and design better schemas and SQL statements. It is a core skill that every database professional should continuously develop.