[Solved] You have a table logs(id, event_type, created_at, details) and want to... Which index strategy best enables index-only scans for queries like:SELECT created_at FROM logs WHERE event_type = 'error'; | PostgreSQL

PostgreSQL - Indexing Strategies

You have a table logs(id, event_type, created_at, details) and want to speed up queries filtering by event_type and selecting created_at. Which index strategy best enables index-only scans for queries like:

SELECT created_at FROM logs WHERE event_type = 'error';

ACREATE INDEX idx_logs_event_created ON logs (event_type, created_at);

BCREATE INDEX idx_logs_created ON logs (created_at);

CCREATE INDEX idx_logs_event ON logs (event_type) INCLUDE (created_at);

DCREATE INDEX idx_logs_details ON logs (details);

Step-by-Step Solution

Solution:

Step 1: Analyze query columns
The query filters on 'event_type' and selects 'created_at'.
Step 2: Choose index covering filter and selected columns
CREATE INDEX idx_logs_event ON logs (event_type) INCLUDE (created_at); indexes 'event_type' and includes 'created_at', covering all needed columns for an index-only scan.
Step 3: Compare other options
CREATE INDEX idx_logs_event_created ON logs (event_type, created_at); indexes both columns but does not use INCLUDE, which is fine but may be larger. CREATE INDEX idx_logs_created ON logs (created_at); and CREATE INDEX idx_logs_details ON logs (details); do not cover filter and select columns properly.
Final Answer:
CREATE INDEX idx_logs_event ON logs (event_type) INCLUDE (created_at); -> Option C
Quick Check:
Cover filter and select columns with INCLUDE = C [OK]

Quick Trick: Include selected columns, index filter column first [OK]

Common Mistakes:

Choosing index without filter column first
Not including selected columns in index
Indexing irrelevant columns

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Which index strategy best enables index-only scans for queries like:

Step 1: Analyze query columns

Step 2: Choose index covering filter and selected columns

Step 3: Compare other options

Final Answer:

Quick Check:

Want More Practice?