Query execution plans in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a query execution plan?

A query execution plan is a detailed step-by-step guide created by a database system that shows how it will retrieve data for a specific query. It helps understand how the database processes the query.

beginner

Why are query execution plans important?

They help database users and administrators see how a query runs, identify slow parts, and find ways to make the query faster and more efficient.

intermediate

What are common operations shown in a query execution plan?

Common operations include table scans, index scans, joins, sorting, filtering, and aggregations. These show how data is accessed and combined.

beginner

How can you view a query execution plan in a database?

Most databases have commands or tools to show the plan. For example, in SQL Server you can use SET SHOWPLAN_ALL ON, and in PostgreSQL you use EXPLAIN before your query.

beginner

What does an index scan in a query execution plan mean?

An index scan means the database uses an index to find rows quickly instead of looking at every row in the table, which usually makes the query faster.

What is the main purpose of a query execution plan?

A To store data permanently

B To create new tables

C To show how the database will run a query

D To backup the database

A query execution plan explains the steps the database will take to execute a query.

Which operation in a query execution plan means the database reads every row in a table?

A Index scan

B Table scan

C Join

D Sort

A table scan means the database reads all rows, which can be slow for large tables.

Which command is commonly used to see a query execution plan in PostgreSQL?

A EXPLAIN

B SHOW PLAN

C DESCRIBE

D SELECT PLAN

The EXPLAIN command shows the execution plan for a query in PostgreSQL.

What does using an index in a query execution plan usually do?

A Makes the query faster

B Creates a new table

C Deletes data

D Slows down the query

Indexes help the database find data faster, improving query speed.

Which of these is NOT typically shown in a query execution plan?

A Filtering rows

B Sorting data

C Joining tables

D Backing up data

Backing up data is not part of query execution plans; plans focus on data retrieval steps.

Explain what a query execution plan is and why it is useful.

Think about how the database decides to get data.

Describe two common operations you might see in a query execution plan and what they mean.

Consider how the database reads data from tables.