Databases Intermediate

What is EXPLAIN Plan?

A database command that shows how the query planner will execute a SQL query, revealing join methods, scan types, and estimated costs.

EXPLAIN shows the execution plan the database optimizer chose for a query. In PostgreSQL, EXPLAIN ANALYZE actually runs the query and shows real execution times alongside estimates. Key elements include scan types (Seq Scan, Index Scan, Bitmap Scan), join methods (Nested Loop, Hash Join, Merge Join), sort operations, and cost estimates. Understanding EXPLAIN output is essential for query optimization — it reveals missing indexes, inefficient joins, and poor cardinality estimates. The BUFFERS option shows I/O statistics for identifying cache misses.

A technique where the database maintains multiple versions of data to allow concurrent reads and writes without locking.

SQL

Structured Query Language — the standard language for managing and querying data in relational databases.

Materialized View

A database object that stores the precomputed result of a query, offering faster reads at the cost of periodic refresh.

Write-Ahead Log (WAL)

A technique where changes are first written to a log before being applied to the database, ensuring crash recovery and data integrity.

Stored Procedure

A precompiled collection of SQL statements stored in the database that can be executed as a single unit.

Index

A data structure that improves the speed of data retrieval operations on database tables at the cost of additional storage.

View All Databases Terms →