Skip to Content
SQL Advanced: Queries That Hold Up Under Load
Advanced
0 likes
Courses
SQL Advanced: Queries That Hold Up Under Load

Check Yourself: Advanced Query Shapes

Five scenario questions on the two parts of a recursive CTE, cycles, walking up a tree, JSONB operators and GIN. If you miss more than one, reread the lessons in this section.

5 min SQLDatabasesTesting and DebuggingPerformance Optimization Advanced
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. What are the two parts of a recursive CTE, and when does it stop?
Two independent queries that run at the same time. It stops after a fixed number of rounds. An anchor that runs once and a recursive term that runs again on the previous round. It stops when a round returns no rows. A SELECT and an index. It stops when the index is full. A parent and a child table. It stops at the end of the table.
2. An org-chart query with WITH RECURSIVE never finishes. Someone saved a row so that two employees are each other's manager. Which change lets the query end?
Raise work_mem. Add an index on manager_id. Add the CYCLE clause, or a depth limit, so a row that was already seen is not followed again. Change the join to a LEFT JOIN.
3. You have a category row and want everything above it, up to the top. How does the recursive term join?
The next row is the one whose category_id equals the current row's parent_id. The recursive term must not join at all. Every row of the table is joined to the current row. The next row is the one whose parent_id equals the current row's category_id.
4. WHERE specs -> 'brand' = 'Cebu Co' fails with an invalid JSON error. Why?
The column needs a GIN index first. The arrow returns jsonb, so the text on the right is read as JSON and Cebu Co is not valid JSON. Use ->> to compare text. The word Cebu Co has a space in it. jsonb cannot be compared with anything.
5. A GIN index exists on the payload column, but WHERE payload ->> 'type' = 'refund' still reads the whole table. What are your options?
Cast the column to text first. Run VACUUM FULL. Write the filter as payload @> a JSON value, or add a B-tree index on the expression payload ->> 'type'. Drop the GIN index, because it blocks the query.

About

Five scenario questions on the two parts of a recursive CTE, cycles, walking up a tree, JSONB operators and GIN. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
09/18/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.