Evaluate the Impact of Indexing on Query Execution

Comparing Query Plans with and without Indexes

Write two PostgreSQL queries using EXPLAIN to compare query plans for a query run on an indexed column versus a non-indexed column.

Solution:

-- Query plan for a filter on an indexed column.
EXPLAIN 
SELECT * FROM Employees 
WHERE email = '[email protected]';

-- Query plan for a filter on a non-indexed column.
EXPLAIN 
SELECT * FROM Employees 
WHERE nickname = 'emp123';

Explanation:

• Purpose of the Query:
• To compare how PostgreSQL executes similar queries on indexed versus non-indexed columns.
• Highlights the performance benefits of indexing.
• Key Components:
• Two separate EXPLAIN statements illustrating different filter conditions.
• Indexed column (email) versus non-indexed column (nickname).
• Real-World Application:
• Aids database administrators in making informed decisions about which columns should be indexed.

Notes:

• Compare the estimated costs and row estimates in both plans to see the impact of indexing.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query using EXPLAIN to compare the execution plan for filtering on an indexed "email" column versus a non-indexed "nickname" column in the "Users" table.
• Write a PostgreSQL query using EXPLAIN to compare the execution plan for a query on the "Orders" table with an index on "order_date" and without that index.
• Write a PostgreSQL query using EXPLAIN to analyze a JOIN query on an indexed foreign key column versus a non-indexed column in the "Transactions" table.
• Write a PostgreSQL query using EXPLAIN to compare cost estimates for filtering on an indexed "zipcode" column versus a non-indexed "city" column in the "Addresses" table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Analyzing Window Function Performance with EXPLAIN ANALYZE.
Next PostgreSQL Exercise: Analyzing Recursive CTE Performance with EXPLAIN ANALYZE.