Indexing JSONB with GIN in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Query performance with GIN index on JSONB

Given a table products with a details column of type JSONB, which query will use the GIN index created as CREATE INDEX idx_gin_details ON products USING GIN (details); to efficiently find products where the color is "red"?

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, details JSONB);
CREATE INDEX idx_gin_details ON products USING GIN (details);

-- Which query uses the GIN index efficiently?

A SELECT * FROM products WHERE details @> '{"color": "red"}';

B SELECT * FROM products WHERE details->>'color' = 'red';

C SELECT * FROM products WHERE details ? 'color';

D SELECT * FROM products WHERE details->'color' = 'red';

💡 Hint

GIN indexes on JSONB support containment operators like @>.

The @> operator checks if the left JSONB contains the right JSONB. This operator is supported by GIN indexes on JSONB columns, so query A uses the index efficiently. Queries using ->> or -> operators do not use the GIN index for filtering.

🧠 Conceptual

intermediate

Understanding GIN index behavior on JSONB

What does a GIN index on a JSONB column index internally to speed up queries?

A It indexes only the top-level keys of the JSONB document.

B It indexes only numeric values inside the JSONB document.

C It indexes all keys and values inside the JSONB document separately.

D It indexes the entire JSONB document as a single string.

💡 Hint

Think about how GIN indexes support containment queries.

GIN indexes on JSONB break down the JSON document into individual keys and values and index them separately. This allows efficient containment queries using operators like @>.

📝 Syntax

advanced

Correct syntax for creating a GIN index on JSONB path ops

Which of the following is the correct syntax to create a GIN index on a JSONB column data using the jsonb_path_ops operator class?

A CREATE INDEX idx_data_path ON mytable USING GIN (data jsonb_path_ops);

B CREATE INDEX idx_data_path ON mytable USING GIN (data jsonb_path_ops());

C ;)spo_htap_bnosj atad( NIG GNISU elbatym NO htap_atad_xdi XEDNI ETAERC

D REATE INDEX idx_data_path ON mytable USING GIN (data jsonb_path_ops);

💡 Hint

Operator classes are specified without parentheses.

The correct syntax is CREATE INDEX idx ON table USING GIN (column jsonb_path_ops); without parentheses after the operator class name.

❓ optimization

advanced

Choosing the right GIN operator class for JSONB indexing

You want to optimize queries that check if a JSONB column contains specific key-value pairs but do not need to query array elements inside JSON. Which GIN operator class should you choose?

A jsonb_path_ops

B jsonb_array_ops

C jsonb_ops

D jsonb_full_ops

💡 Hint

One operator class is smaller and faster but supports fewer query types.

jsonb_path_ops indexes only the top-level keys and values, making it smaller and faster for containment queries that do not involve arrays. jsonb_ops supports more query types but is larger.

🔧 Debug

expert

Diagnosing why a GIN index is not used for a JSONB query

You created a GIN index on a JSONB column info using CREATE INDEX idx_info ON users USING GIN (info);. However, the query SELECT * FROM users WHERE info->>'status' = 'active'; does not use the index and is slow. Why?

A The index was created on the wrong column; it should be on <code>info->>'status'</code>.

B The operator <code>->></code> does not use the GIN index; only containment operators like <code>@></code> do.

C GIN indexes do not support JSONB columns, only B-tree indexes do.

D The query syntax is invalid and causes the planner to ignore the index.

💡 Hint

Consider which JSONB operators are supported by GIN indexes.

GIN indexes on JSONB support containment operators like @>. The ->> operator extracts text and does not use the GIN index. To use the index, rewrite the query with @> operator.