Highlighting with ts_headline in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Basic usage of ts_headline

Given the table documents with a text column content, what is the output of the following query?

SELECT ts_headline('english', content, to_tsquery('english', 'database')) AS headline FROM documents WHERE id = 1;

PostgreSQL

CREATE TABLE documents (id INT, content TEXT);
INSERT INTO documents VALUES (1, 'PostgreSQL is a powerful, open source object-relational database system.');

A PostgreSQL is a powerful, open source object-relational database system.

B PostgreSQL is a powerful, open source object-relational <em>database</em> system.

C PostgreSQL is a powerful, open source object-relational <b>database</b> system.

D PostgreSQL is a powerful, open source object-relational <u>database</u> system.

💡 Hint

ts_headline highlights matching words by default using tags.

The ts_headline function highlights the matched term 'database' in the content using tags by default.

❓ query_result

intermediate

Customizing Highlighting Tags

What will be the output of this query?

SELECT ts_headline('english', content, to_tsquery('english', 'system'), 'StartSel=, StopSel=') AS headline FROM documents WHERE id = 1;

PostgreSQL

CREATE TABLE documents (id INT, content TEXT);
INSERT INTO documents VALUES (1, 'PostgreSQL is a powerful, open source object-relational database system.');

A PostgreSQL is a powerful, open source object-relational database <em>system</em>.

B PostgreSQL is a powerful, open source object-relational database <b>system</b>.

C PostgreSQL is a powerful, open source object-relational database system.

D PostgreSQL is a powerful, open source object-relational database <u>system</u>.

💡 Hint

The options parameter controls the tags used for highlighting.

The StartSel and StopSel options change the highlight tags to and instead of the default and .

🧠 Conceptual

advanced

Understanding ts_headline Behavior with Multiple Matches

If the content contains multiple occurrences of the search term, how does ts_headline decide which occurrences to highlight by default?

A It highlights all occurrences of the search term in the content.

B It highlights only the first occurrence of the search term.

C It highlights occurrences randomly throughout the content.

D It highlights the occurrences closest to the start of the content and limits the output length.

💡 Hint

Consider how ts_headline tries to produce a concise snippet.

ts_headline tries to produce a snippet that includes the search terms near the start and limits the length, so it highlights occurrences closest to the start rather than all.

📝 Syntax

advanced

Correct Syntax for ts_headline with Config and Options

Which of the following is the correct syntax to highlight the word 'search' in the column text_data using ts_headline with English configuration and custom highlight tags <mark> and </mark>?

A SELECT ts_headline('english', text_data, to_tsquery('search'), 'StartSel=<mark>, StopSel=</mark>') FROM my_table;

B SELECT ts_headline('english', text_data, to_tsquery('english', 'search'), 'StartSel=<mark>, StopSel=</mark>') FROM my_table;

C SELECT ts_headline('english', text_data, 'search', 'StartSel=<mark>, StopSel=</mark>') FROM my_table;

D SELECT ts_headline(text_data, 'english', to_tsquery('search'), 'StartSel=<mark>, StopSel=</mark>') FROM my_table;

💡 Hint

Remember the order of parameters: config, document, query, options.

The correct syntax requires the config name first, then the text column, then a tsquery (with config), and then the options string.

❓ optimization

expert

Optimizing ts_headline for Large Text Columns

You have a table with a large text column and want to highlight search terms efficiently using ts_headline. Which approach is best to improve performance when running many queries?

A Create a GIN index on the tsvector of the text column and use to_tsquery in the WHERE clause before calling ts_headline.

B Call ts_headline directly on the entire text column without any filtering.

C Use ts_headline with a very large StartSel and StopSel option to highlight more text.

D Store pre-highlighted text in a separate column and update it manually.

💡 Hint

Think about how to reduce the number of rows processed by ts_headline.

Using a GIN index with a WHERE clause filters rows quickly, so ts_headline only processes relevant rows, improving performance.