[Solved] Given a table products(id, name, price) with an index on (id, price), what will be the output of this query if an index-only scan is used?SELECT id, price FROM products WHERE price > 100; | PostgreSQL

PostgreSQL - Indexing Strategies

Given a table products(id, name, price) with an index on (id, price), what will be the output of this query if an index-only scan is used?

SELECT id, price FROM products WHERE price > 100;

ARows with id and price where price is greater than 100

BRows with id and name where price is greater than 100

CAll rows regardless of price

DError due to missing columns in index

Step-by-Step Solution

Solution:

Step 1: Check index columns and query columns
The index covers 'id' and 'price'. The query selects 'id' and 'price' and filters on 'price'. All needed columns are in the index.
Step 2: Determine output with index-only scan
Since the index covers the query, PostgreSQL can use an index-only scan and return rows with 'id' and 'price' where price > 100.
Final Answer:
Rows with id and price where price is greater than 100 -> Option A
Quick Check:
Index covers selected columns = A [OK]

Quick Trick: Index-only scan returns only indexed columns requested [OK]

Common Mistakes:

Expecting columns not in index to appear
Assuming error if some columns missing in index but not selected
Thinking all rows are returned regardless of filter

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given a table products(id, name, price) with an index on (id, price), what will be the output of this query if an index-only scan is used?

Step 1: Check index columns and query columns

Step 2: Determine output with index-only scan

Final Answer:

Quick Check:

Want More Practice?