GiST index for geometric and text in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a GiST index on the 'location' column of type 'point'.

PostgreSQL

CREATE INDEX idx_location ON places USING [1] (location);

Drag options to blanks, or click blank then click option'

Agist

Bhash

Cbtree

Dgin

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'btree' which does not support geometric types well.

Using 'hash' which is not suitable for spatial queries.

The GiST index type is used for geometric data types like 'point'. Here, 'gist' is the correct index method.

2fill in blank

medium

Complete the code to create a GiST index on the 'description' column using the appropriate operator class for trigram search.

PostgreSQL

CREATE INDEX idx_description ON documents USING gist (description [1]);

Drag options to blanks, or click blank then click option'

Agist_trgm_ops

Bgin_trgm_ops

Cgist_gin_ops

Dgin_ops

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'gin_trgm_ops' which is for GIN indexes.

Confusing GIN and GiST operator classes.

For trigram search on text columns with GiST indexes, the 'gist_trgm_ops' operator class is used.

3fill in blank

hard

Fix the error in the GiST index creation for a polygon column named 'area'.

PostgreSQL

CREATE INDEX idx_area ON regions USING [1] (area);

Drag options to blanks, or click blank then click option'

Ahash

Bbtree

Cgist

Dgin

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'btree' or 'hash' which do not support polygon types.

Using 'gin' which is for text search and arrays.

Geometric types like polygons require GiST indexes for efficient spatial queries. 'gist' is the correct index type.

4fill in blank

hard

Fill both blanks to create a GiST index on the 'content' column using the correct operator class for trigram search.

PostgreSQL

CREATE INDEX idx_content ON articles USING [1] (content [2]);

Drag options to blanks, or click blank then click option'

Agist

Bgin

Cgin_trgm_ops

Dgist_trgm_ops

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'gin' as index method.

Using 'gin_trgm_ops' which is for GIN indexes.

The GiST index is created using 'gist' and the operator class for trigram search with GiST is 'gist_trgm_ops'. This combination enables trigram indexing with GiST.

5fill in blank

hard

Fill all three blanks to create a GiST index on the 'shape' column of type 'polygon' and a trigram GiST index on the 'summary' column.

PostgreSQL

CREATE INDEX idx_shape ON data USING [1] (shape); CREATE INDEX idx_summary ON data USING [2] (summary [3]);

Drag options to blanks, or click blank then click option'

Abtree

Bgist

Cgin_trgm_ops

Dgist_trgm_ops

Attempts:

3 left

💡 Hint

Common Mistakes

Attempting a single statement for two indexes.

Using 'gin_trgm_ops' instead of 'gist_trgm_ops' for GiST.

Two separate CREATE INDEX statements are required. Use 'gist' for the geometric 'shape' column and 'gist' with 'gist_trgm_ops' for trigram search on 'summary'.