Scan vs query performance in AWS - Visual Side-by-Side Comparison

Process Flow - Scan vs query performance

Start Request

↓

Choose Operation

↓

Query

↓

Use Key

↓

Fetch Items

↓

Return Result

The flow shows how a request chooses between Query and Scan. Query uses keys to fetch items directly. Scan reads the whole table and then filters.

Execution Sample

AWS

Query: Get items where PK = 'User#123'
Scan: Read all items, filter where Age > 30

This example compares Query fetching items by key versus Scan reading all items and filtering.

Process Table

Step	Operation	Action	Items Processed	Items Returned	Performance Impact
1	Query	Use partition key to find items	10	10	Fast, reads only matching items
2	Scan	Read entire table	1000	50	Slow, reads all items then filters
3	Query	Return matched items	10	10	Efficient, minimal read
4	Scan	Filter items after read	1000	50	Inefficient, high read cost
5	Query	Complete	10	10	Low latency, low cost
6	Scan	Complete	1000	50	High latency, high cost

💡 Query stops after reading matching keys; Scan stops after reading entire table.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Items Processed (Query)	0	10	10	10	10	10
Items Processed (Scan)	0	0	1000	1000	1000	1000
Items Returned (Query)	0	0	0	10	10	10
Items Returned (Scan)	0	0	0	0	50	50

Key Moments - 2 Insights

Why does Scan process more items than Query even if fewer items are returned?

Scan reads the entire table (1000 items) before filtering, while Query reads only matching items (10). See execution_table rows 1 and 2.

Does Query always return fewer items than Scan?

Not necessarily. Query returns items matching the key condition, which can be many or few. Scan returns filtered items after reading all. See rows 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, how many items does Query process at step 1?

A 1000

B 10

C 50

D 0

💡 Hint

Check 'Items Processed' column for Query at step 1 in execution_table.

At which step does Scan filter items after reading the whole table?

A Step 4

B Step 2

C Step 3

D Step 1

💡 Hint

Look for 'Filter items after read' action in execution_table.

If the table size doubles, how would Scan's 'Items Processed' change at step 2?

A It stays the same

B It halves

C It doubles

D It becomes zero

💡 Hint

Scan reads the entire table, so 'Items Processed' depends on table size (see variable_tracker).

Concept Snapshot

Scan vs Query Performance:
- Query uses keys to fetch matching items only.
- Scan reads entire table then filters.
- Query is faster and cheaper for targeted reads.
- Scan is slower and costly for large tables.
- Prefer Query when possible for efficiency.

Full Transcript

This visual execution compares Scan and Query operations in AWS DynamoDB. Query uses a partition key to directly fetch matching items, processing only those items. Scan reads the entire table and then filters items, processing many more items than it returns. The execution table shows Query processing 10 items and returning 10, while Scan processes 1000 items but returns only 50. This difference explains why Query is faster and more efficient. Key moments clarify common confusions about item processing and return counts. The quiz tests understanding of these steps and performance impacts. The snapshot summarizes that Query is preferred for performance, while Scan is costly and slower.

Practice

1. Which AWS DynamoDB operation is faster when you know the exact primary key of the item you want to retrieve?

easy

A. Update

B. Scan

C. Query

D. Delete

Solution

1. Step 1: Understand Query operation

   Query retrieves items by specifying the primary key, making it fast and efficient.

2. Step 2: Compare with Scan operation

   Scan reads the entire table, which is slower and more costly when you know the key.

3. Final Answer:

   Query -> Option C

4. Quick Check:

   Known key = Query [OK]

Hint: Use Query for known keys, Scan reads all data [OK]

Common Mistakes:

• Thinking Scan is faster for known keys
• Confusing Update or Delete with data retrieval
• Assuming Query reads the whole table

2. Which of the following is the correct syntax to perform a Query operation in AWS DynamoDB using the AWS CLI?

easy

A. aws dynamodb query --table-name MyTable --key-condition-expression 'Id = :id' --expression-attribute-values '{":id":{"S":"123"}}'

B. aws dynamodb scan --table-name MyTable --key-condition-expression 'Id = :id' --expression-attribute-values '{":id":{"S":"123"}}'

C. aws dynamodb get-item --table-name MyTable --key '{"Id":{"S":"123"}}'

D. aws dynamodb update-item --table-name MyTable --key '{"Id":{"S":"123"}}'

Solution

1. Step 1: Identify Query command syntax

   The Query operation uses 'aws dynamodb query' with key-condition-expression and expression-attribute-values.

2. Step 2: Check other options

   Scan reads the entire table (and does not support --key-condition-expression), get-item retrieves a single item by exact key, update-item modifies items, so only the Query syntax is correct.

3. Final Answer:

   aws dynamodb query --table-name MyTable --key-condition-expression 'Id = :id' --expression-attribute-values '{":id":{"S":"123"}}' -> Option A

4. Quick Check:

   Query syntax uses 'query' command [OK]

Hint: Query uses 'aws dynamodb query' command [OK]

Common Mistakes:

• Using Scan command instead of Query
• Confusing GetItem with Query
• Incorrect expression-attribute-values format

3. Given a DynamoDB table with 1000 items, what will be the main difference in performance between these two operations?

1. Query with KeyConditionExpression on partition key
2. Scan without any filter

medium

A. Both read the same number of items and have similar speed

B. Scan reads fewer items and is faster; Query reads all items and is slower

C. Both read all items but Query is slower due to filtering

D. Query reads fewer items and is faster; Scan reads all items and is slower

Solution

1. Step 1: Analyze Query operation

   Query uses the partition key to directly find matching items, reading fewer items and faster.

2. Step 2: Analyze Scan operation

   Scan reads every item in the table, making it slower and more expensive.

3. Final Answer:

   Query reads fewer items and is faster; Scan reads all items and is slower -> Option D

4. Quick Check:

   Query = fewer reads, Scan = full table [OK]

Hint: Query targets keys, Scan reads whole table [OK]

Common Mistakes:

• Assuming Scan is faster than Query
• Thinking Query reads all items
• Ignoring cost differences

4. You wrote this DynamoDB Scan command but it is very slow:

aws dynamodb scan --table-name Users

What is the best way to improve performance if you only want users with UserId = '123'?

medium

A. Replace Scan with Query using KeyConditionExpression on UserId

B. Add a FilterExpression to the Scan command

C. Increase the Read Capacity Units of the table

D. Use UpdateItem to modify the UserId

Solution

1. Step 1: Identify problem with Scan

   Scan reads all items, causing slowness when filtering after reading.

2. Step 2: Use Query for known key

   Query with KeyConditionExpression on UserId reads only matching items, improving speed.

3. Final Answer:

   Replace Scan with Query using KeyConditionExpression on UserId -> Option A

4. Quick Check:

   Known key = Query, not Scan [OK]

Hint: Use Query with key condition, not Scan with filter [OK]

Common Mistakes:

• Adding filter to Scan instead of using Query
• Increasing capacity without optimizing query
• Using UpdateItem unrelated to read performance

5. You have a DynamoDB table with 1 million items. You need to retrieve all items where the attribute 'Status' equals 'Active'. The table's primary key is 'UserId'. Which approach gives the best performance and cost efficiency?

hard

A. Use Scan with FilterExpression 'Status = Active' to get all matching items

B. Create a Global Secondary Index (GSI) on 'Status' and use Query on the GSI

C. Use Query on the primary key 'UserId' with FilterExpression 'Status = Active'

D. Use Scan without any filter and filter results in your application code

Solution

1. Step 1: Understand Scan with filter

   Scan reads all 1 million items, filtering after reading, which is slow and costly.

2. Step 2: Use GSI for efficient querying

   Creating a GSI on 'Status' allows Query to directly find 'Active' items efficiently.

3. Step 3: Evaluate other options

   Query on primary key can't filter by 'Status' efficiently; scanning without filter wastes resources.

4. Final Answer:

   Create a Global Secondary Index (GSI) on 'Status' and use Query on the GSI -> Option B

5. Quick Check:

   GSI + Query = best performance for non-key attribute [OK]

Hint: Use GSI and Query for non-key attribute filtering [OK]

Common Mistakes:

• Using Scan with filter on large tables
• Querying primary key with filter instead of GSI
• Filtering in application after full Scan