[Solved] You want to create a GiST index on a table documents with a column content... Which of the following statements correctly creates the index and allows efficient search for the phrase 'open source'? | PostgreSQL

PostgreSQL - Indexing Strategies

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'?

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

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

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

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

Step-by-Step Solution

Solution:

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');
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.
Final Answer:
CREATE INDEX content_gist_idx ON documents USING gist (content); SELECT * FROM documents WHERE content @@ to_tsquery('open & source'); -> Option A
Quick Check:
GiST index + tsvector column + @@ to_tsquery = efficient search [OK]

Quick Trick: 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

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Which of the following statements correctly creates the index and allows efficient search for the phrase 'open source'?

Step 1: Create GiST index on tsvector column

Step 2: Use proper full-text search query

Final Answer:

Quick Check:

Want More Practice?