Index-only scans mental model in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the table using an index scan.

PostgreSQL

SELECT * FROM users WHERE [1] = 'active';

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

Astatus

Bid

Cname

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column not indexed in the WHERE clause.

Selecting columns not covered by the index.

The status column is used in the WHERE clause to filter rows. If an index exists on status, PostgreSQL can use an index scan to quickly find rows where status is 'active'.

2fill in blank

medium

Complete the code to create an index that supports index-only scans on the 'status' column.

PostgreSQL

CREATE INDEX idx_users_[1] ON users([2]);

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

Astatus

Bid

Cemail

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Creating an index on the wrong column.

Using a different column name in the index and query.

Creating an index on the status column allows PostgreSQL to perform index-only scans when queries filter by status.

3fill in blank

hard

Fix the error in the query to enable an index-only scan by selecting only indexed columns.

PostgreSQL

SELECT [1] FROM users WHERE status = 'active';

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

A*

Bemail

Cid

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting all columns with '*', which prevents index-only scans.

Selecting columns not covered by the index.

Index-only scans work when the query selects only columns present in the index. Selecting id (assuming it is indexed) allows the scan to avoid accessing the table.

4fill in blank

hard

Fill both blanks to create a covering index that supports index-only scans for queries filtering by 'status' and selecting 'email'.

PostgreSQL

CREATE INDEX idx_users_[1] ON users(status, [2]);

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

Astatus_email

Bstatus

Cemail

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Creating an index on only one column when two are needed.

Naming the index unrelated to its columns.

A multi-column index on status and email allows index-only scans for queries filtering by status and selecting email.

5fill in blank

hard

Fill all three blanks to write a query that uses an index-only scan by selecting only indexed columns and filtering by 'status'.

PostgreSQL

SELECT [1], [2] FROM users WHERE [3] = 'active';

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

Aid

Bemail

Cstatus

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not in the index.

Filtering by a column not indexed.

Selecting id and email columns and filtering by status (all indexed) enables an index-only scan, avoiding table access.