Arrow operators (-> and ->>) in PostgreSQL - Time & Space Complexity

Time Complexity: Arrow operators (-> and ->>)

O(n)

Understanding Time Complexity

We want to understand how the time it takes to get data using arrow operators grows as the data size grows.

Specifically, how does using these operators to access JSON data affect performance?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT data->'address'->>'city' AS city_name
FROM users
WHERE data->>'status' = 'active';
    

This query extracts the city name from a JSON column for users with an active status.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The database scans each row in the users table and applies the arrow operators to the JSON data.
• How many times: Once per row, so the number of times equals the number of rows scanned.

How Execution Grows With Input

As the number of rows grows, the database must apply the arrow operators to more JSON objects.

Input Size (n)	Approx. Operations
10	10 JSON extractions
100	100 JSON extractions
1000	1000 JSON extractions

Pattern observation: The work grows directly with the number of rows scanned.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line as the number of rows increases.

Common Mistake

[X] Wrong: "Using arrow operators is instant and does not depend on data size."

[OK] Correct: Each row's JSON data must be accessed separately, so more rows mean more work.

Interview Connect

Understanding how JSON operators scale helps you explain query performance clearly and shows you know how data size affects speed.

Self-Check

"What if we add an index on the JSON field used in the WHERE clause? How would the time complexity change?"