Sequential scan vs index scan in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select rows from the table using a sequential scan.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > [1];

Drag options to blanks, or click blank then click option'

ANULL

B'50000'

Csalary

D50000

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around numeric values causing type mismatch.

Using column names instead of values in the WHERE clause.

The value 50000 is a number and should not be quoted in SQL when comparing numeric columns like salary.

2fill in blank

medium

Complete the code to create an index on the salary column to speed up queries.

PostgreSQL

CREATE INDEX idx_salary ON employees([1]);

Drag options to blanks, or click blank then click option'

Asalary

Bname

Cdepartment

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Creating index on unrelated columns that don't improve query speed.

Trying to index multiple columns without specifying them.

Creating an index on the salary column helps queries filtering by salary run faster.

3fill in blank

hard

Fix the error in the query to force a sequential scan by disabling index scans.

PostgreSQL

SET enable_indexscan = [1]; EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

Drag options to blanks, or click blank then click option'

Aoff

Bon

Ctrue

Dfalse

Attempts:

3 left

💡 Hint

Common Mistakes

Setting enable_indexscan to on which enables index scans.

Using true/false instead of on/off which are the correct keywords.

Setting enable_indexscan to off disables index scans, forcing PostgreSQL to use sequential scans.

4fill in blank

hard

Fill both blanks to create a query that uses an index scan on the salary column with a condition.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE [1] [2] 60000;

Drag options to blanks, or click blank then click option'

Asalary

B>

C<

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name in the WHERE clause.

Using the less than operator which changes the filter condition.

The query filters rows where salary is greater than 60000, which can use an index on salary.

5fill in blank

hard

Fill all three blanks to create a query that uses an index scan on the department column with a specific value.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE [1] = '[2]' ORDER BY [3];

Drag options to blanks, or click blank then click option'

Adepartment

BSales

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names in WHERE or ORDER BY.

Forgetting to quote string values in SQL.

The query filters employees in the Sales department and orders results by department, which can use an index on department.