GiST index for geometric and text in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GiST index usage on geometric data

Consider a PostgreSQL table shapes with a box column storing geometric boxes. A GiST index is created on this column. What will be the output of the following query?

SELECT count(*) FROM shapes WHERE box && '((1,1),(5,5))'::box;

Assume the table has these rows:

• box '((0,0),(2,2))'
• box '((3,3),(6,6))'
• box '((6,6),(8,8))'

PostgreSQL

CREATE TABLE shapes (id SERIAL PRIMARY KEY, box box);
INSERT INTO shapes (box) VALUES ('((0,0),(2,2))'), ('((3,3),(6,6))'), ('((6,6),(8,8))');
CREATE INDEX shapes_box_gist_idx ON shapes USING gist(box);

SELECT count(*) FROM shapes WHERE box && '((1,1),(5,5))'::box;

A 1

B 3

C 2

D 0

💡 Hint

The && operator checks if two boxes overlap.

The boxes '((0,0),(2,2))' and '((3,3),(6,6))' overlap with '((1,1),(5,5))'. The third box does not overlap. So the count is 2.

🧠 Conceptual

intermediate

Understanding GiST index benefits for text search

Why is a GiST index useful for full-text search in PostgreSQL?

A It automatically corrects spelling mistakes in queries.

B It supports indexing complex data types like tsvector for fast text search.

C It compresses text data to save disk space.

D It stores full text documents directly for faster retrieval.

💡 Hint

Think about what GiST indexes can do with special data types.

GiST indexes support indexing of complex data types such as tsvector, which is used for full-text search, enabling fast search operations.

📝 Syntax

advanced

Correct GiST index creation for text search

Which of the following SQL statements correctly creates a GiST index on a tsvector column named document_vector in a table articles?

A CREATE INDEX articles_gist_idx ON articles USING gist(document_vector);

B CREATE INDEX articles_gist_idx ON articles USING btree(document_vector);

C CREATE INDEX articles_gist_idx ON articles USING gist(document_vector text_pattern_ops);

D CREATE INDEX articles_gist_idx ON articles USING hash(document_vector);

💡 Hint

GiST indexes require the gist keyword and do not use operator classes like text_pattern_ops for tsvector.

Option A correctly creates a GiST index on the tsvector column. Option A uses btree which is not suitable. Option A incorrectly uses operator class. Option A uses hash which is not supported for tsvector.

❓ optimization

advanced

Optimizing spatial queries with GiST index

You have a table locations with a point column storing coordinates. You want to find all points within a rectangle quickly. Which query will best use a GiST index on the point column?

A SELECT * FROM locations WHERE point <@ box '((1,1),(5,5))';

B SELECT * FROM locations WHERE point = '(3,3)';

C SELECT * FROM locations WHERE point && box '((1,1),(5,5))';

D SELECT * FROM locations WHERE point < box '((1,1),(5,5))';

💡 Hint

Look for the operator that checks if a point is inside a box.

The <@ operator checks if a point is contained within a box and can use a GiST index efficiently. The && operator is for box overlap, not point containment.

🔧 Debug

expert

Diagnosing GiST index usage issue on text search

A developer created a GiST index on a tsvector column content_vector in table documents. However, queries using to_tsvector('english', content) in the WHERE clause are slow and do not use the index. Why?

A The <code>content_vector</code> column must be of type <code>text</code> for GiST index to work.

B GiST indexes do not support <code>tsvector</code> columns for text search.

C The index must be created with the <code>GIN</code> method, not GiST, to be used.

D The index is on <code>content_vector</code>, but the query uses <code>to_tsvector('english', content)</code> directly, so the index is not used.

💡 Hint

Check if the query expression matches the indexed column.

The index is on the content_vector column, but the query uses to_tsvector('english', content) which is a function call and not the indexed column itself. To use the index, the query must filter on the indexed column or create an expression index matching the query.