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.

Practice

1. What is the main purpose of a GiST index in PostgreSQL?

easy

A. To enforce data integrity constraints

B. To store data in a compressed format

C. To backup the database automatically

D. To speed up searches on complex data types like geometric shapes and text

Solution

1. Step 1: Understand GiST index purpose

   GiST indexes are designed to speed up searches on complex data types such as geometric shapes and full-text search data.

2. Step 2: Compare options

   Options A, B, and D describe other database features unrelated to GiST indexes.

3. Final Answer:

   To speed up searches on complex data types like geometric shapes and text -> Option D

4. Quick Check:

   GiST index = speed up complex data search [OK]

Hint: GiST indexes speed up complex data searches like shapes and text [OK]

Common Mistakes:

• Confusing GiST with data compression
• Thinking GiST enforces constraints
• Assuming GiST is for backups

2. Which of the following is the correct syntax to create a GiST index named places_location_gist on a column named location in table places?

easy

A. CREATE INDEX ON places USING gist (location);

B. CREATE INDEX places_location_gist ON places USING gist (location);

C. CREATE gist INDEX ON places (location);

D. CREATE INDEX places_location_gist ON gist (location);

Solution

1. Step 1: Recall correct CREATE INDEX syntax

   The correct syntax is: CREATE INDEX index_name ON table_name USING gist (column_name);

2. Step 2: Match options to syntax

   CREATE INDEX places_location_gist ON places USING gist (location); matches the correct syntax with index name, table, USING gist, and column.

3. Final Answer:

   CREATE INDEX places_location_gist ON places USING gist (location); -> Option B

4. Quick Check:

   CREATE INDEX ... ON table USING gist (column) [OK]

Hint: Remember: CREATE INDEX name ON table USING gist (column) [OK]

Common Mistakes:

• Omitting index name
• Placing USING gist in wrong position
• Using 'CREATE gist INDEX' which is invalid

3. Given the table shapes with a box column of type box, and a GiST index created on box, what will the query below return?

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

The operator && means "overlaps" for geometric types.

medium

A. All rows where the box overlaps the box from (1,1) to (4,4)

B. All rows where the box is exactly equal to (1,1),(4,4)

C. All rows where the box is contained inside (1,1),(4,4)

D. Syntax error due to wrong operator

Solution

1. Step 1: Understand the && operator for box type

   The && operator checks if two boxes overlap in PostgreSQL geometric types.

2. Step 2: Interpret the query condition

   The query selects rows where the box column overlaps the box defined by coordinates (1,1) and (4,4).

3. Final Answer:

   All rows where the box overlaps the box from (1,1) to (4,4) -> Option A

4. Quick Check:

   box && box = overlap check [OK]

Hint: && means overlap for geometric types in PostgreSQL [OK]

Common Mistakes:

• Confusing overlap with equality
• Thinking && means containment
• Assuming syntax error with &&

4. You created a GiST index on a text column for full-text search but your queries are still slow. Which of the following is a likely cause?

medium

A. You created the index on the wrong table

B. GiST indexes do not support text search

C. You forgot to use the @@ operator in your WHERE clause

D. You need to vacuum the table before using the index

Solution

1. Step 1: Understand GiST index usage for full-text search

   GiST indexes support full-text search but queries must use the @@ operator to use the index.

2. Step 2: Analyze options

   The most likely cause is forgetting the @@ operator, which prevents index usage. GiST indexes do not support text search is false--GiST supports text search. Options A and D are possible but less directly related to index usage.

3. Final Answer:

   You forgot to use the @@ operator in your WHERE clause -> Option C

4. Quick Check:

   Full-text search needs @@ operator to use GiST index [OK]

Hint: Use @@ operator in WHERE to leverage GiST full-text index [OK]

Common Mistakes:

• Assuming GiST doesn't support text search
• Not using @@ operator in queries
• Ignoring index table or vacuum issues

5. You want to create a GiST index on a table documents with a column content of type tsvector for fast full-text search. Which of the following statements correctly creates the index and allows efficient search for the phrase 'open source'?

hard

A. CREATE INDEX content_gist_idx ON documents USING gist (content);
SELECT * FROM documents WHERE content @@ to_tsquery('open & source');

B. CREATE INDEX content_gist_idx ON documents USING gist (content);
SELECT * FROM documents WHERE content LIKE '%open source%';

C. CREATE INDEX content_gist_idx ON documents USING btree (content);
SELECT * FROM documents WHERE content @@ to_tsquery('open & source');

D. CREATE INDEX content_gist_idx ON documents USING gist (content);
SELECT * FROM documents WHERE to_tsvector(content) @@ to_tsquery('open & source');

Solution

1. Step 1: Create GiST index on tsvector column

   The correct syntax is to create a GiST index on the tsvector column directly, as in CREATE INDEX content_gist_idx ON documents USING gist (content); SELECT * FROM documents WHERE content @@ to_tsquery('open & source');

2. Step 2: Use proper full-text search query

   CREATE INDEX content_gist_idx ON documents USING gist (content); SELECT * FROM documents WHERE content @@ to_tsquery('open & source'); uses content @@ to_tsquery('open & source'), which is the correct way to search for both words with AND logic. CREATE INDEX content_gist_idx ON documents USING gist (content); SELECT * FROM documents WHERE content LIKE '%open source%'; uses LIKE, which does not use the index. CREATE INDEX content_gist_idx ON documents USING btree (content); SELECT * FROM documents WHERE content @@ to_tsquery('open & source'); uses btree index, which is not suitable for tsvector. CREATE INDEX content_gist_idx ON documents USING gist (content); SELECT * FROM documents WHERE to_tsvector(content) @@ to_tsquery('open & source'); applies to_tsvector on the column again, which is unnecessary and inefficient.

3. Final Answer:

   CREATE INDEX content_gist_idx ON documents USING gist (content); SELECT * FROM documents WHERE content @@ to_tsquery('open & source'); -> Option A

4. Quick Check:

   GiST index + tsvector column + @@ to_tsquery = efficient search [OK]

Hint: Index tsvector column and query with @@ and to_tsquery [OK]

Common Mistakes:

• Using LIKE instead of @@ for full-text search
• Creating btree index on tsvector column
• Applying to_tsvector again in WHERE clause