[Solved] Given a table 'products' with 1 million rows and an index on 'category_id', what scan will PostgreSQL likely use for this query?SELECT * FROM products WHERE category_id = 10; | PostgreSQL

PostgreSQL - Performance Tuning

Given a table 'products' with 1 million rows and an index on 'category_id', what scan will PostgreSQL likely use for this query?

SELECT * FROM products WHERE category_id = 10;

ASequential scan because the table is large.

BSequential scan because indexes are ignored on numeric columns.

CIndex scan because the query filters on an indexed column.

DIndex scan only if the table has less than 1000 rows.

Step-by-Step Solution

Solution:

Step 1: Analyze query filter and index
The query filters on 'category_id' which has an index, so PostgreSQL can use the index to find matching rows quickly.
Step 2: Consider table size and scan choice
Even with a large table, index scan is preferred if the filter is selective and an index exists.
Final Answer:
Index scan because the query filters on an indexed column. -> Option C
Quick Check:
Scan choice with index = B [OK]

Quick Trick: Index scan used when filtering on indexed columns [OK]

Common Mistakes:

Master "Performance Tuning" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Given a table 'products' with 1 million rows and an index on 'category_id', what scan will PostgreSQL likely use for this query?