Bird
Raised Fist0
PostgreSQLquery~5 mins

@@ match operator in PostgreSQL - Time & Space Complexity

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Time Complexity: @@ match operator
O(n)
Understanding Time Complexity

We want to understand how the time it takes to search text using the @@ match operator changes as the amount of data grows.

How does the search time increase when we have more text to check?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.


SELECT *
FROM documents
WHERE content_vector @@ to_tsquery('search & term');

This query finds all rows in the documents table where the content_vector matches the search terms using full-text search.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

  • Primary operation: Checking the search condition against each document's text vector.
  • How many times: Once per document row, but optimized by an index if available.
How Execution Grows With Input

As the number of documents grows, the search checks more entries, but indexes help keep it efficient.

Input Size (n)Approx. Operations
10About 10 checks, quickly filtered by index
100About 100 checks, still fast with index
1000About 1000 checks, index keeps search efficient

Pattern observation: The search time grows roughly in proportion to the number of matching entries, but indexes prevent a full scan, keeping it close to linear or better.

Final Time Complexity

Time Complexity: O(n)

This means the search time grows roughly in proportion to the number of documents, but indexes help keep it efficient.

Common Mistake

[X] Wrong: "The @@ operator always searches instantly no matter how much data there is."

[OK] Correct: Without an index, the search must check every document, which takes longer as data grows.

Interview Connect

Understanding how text search scales helps you explain how databases handle large amounts of text efficiently, a useful skill in many real projects.

Self-Check

"What if we removed the index on content_vector? How would the time complexity change?"

Practice

(1/5)
1.

What does the @@ operator do in PostgreSQL?

easy
A. It concatenates two text strings.
B. It compares two numeric values for equality.
C. It matches a text column against a full-text search query.
D. It checks if a value is NULL.

Solution

  1. Step 1: Understand the purpose of @@ operator

    The @@ operator is used in PostgreSQL for full-text search to match text columns against search queries.

  2. Step 2: Compare with other options

    Other options describe unrelated operations like numeric comparison, string concatenation, or NULL checks, which are not related to @@.

  3. Final Answer:

    It matches a text column against a full-text search query. -> Option C

  4. Quick Check:

    @@ = full-text match [OK]
Hint: Remember: @@ is for full-text search matching [OK]
Common Mistakes:
  • Confusing @@ with equality operator =
  • Thinking @@ concatenates strings
  • Assuming @@ checks for NULL
2.

Which of the following is the correct syntax to use the @@ operator with to_tsquery in PostgreSQL?

SELECT * FROM articles WHERE content @@ to_tsquery('search_term');
easy
A. SELECT * FROM articles WHERE content @@ to_tsquery('search_term');
B. SELECT * FROM articles WHERE content = to_tsquery('search_term');
C. SELECT * FROM articles WHERE content @@ 'search_term';
D. SELECT * FROM articles WHERE content LIKE to_tsquery('search_term');

Solution

  1. Step 1: Identify correct usage of @@ with to_tsquery

    The @@ operator requires the right side to be a tsquery, which is created by to_tsquery().

  2. Step 2: Check each option

    SELECT * FROM articles WHERE content @@ to_tsquery('search_term'); correctly uses content @@ to_tsquery('search_term'). Options B, C, and D misuse operators or omit to_tsquery().

  3. Final Answer:

    SELECT * FROM articles WHERE content @@ to_tsquery('search_term'); -> Option A

  4. Quick Check:

    Use @@ to_tsquery() syntax [OK]
Hint: Always use to_tsquery() on right side of @@ [OK]
Common Mistakes:
  • Using = instead of @@
  • Passing plain string without to_tsquery()
  • Using LIKE operator for full-text search
3.

Given the table documents with a text_content column, what will this query return?

SELECT id FROM documents WHERE text_content @@ to_tsquery('cat & dog');

Assuming the table has these rows:

  • 1: 'The cat chased the dog.'
  • 2: 'A dog and a cat are friends.'
  • 3: 'The cat is sleeping.'
medium
A. []
B. [1, 2]
C. [3]
D. [1, 2, 3]

Solution

  1. Step 1: Understand the query condition

    The query uses to_tsquery('cat & dog'), which means both 'cat' AND 'dog' must be present in text_content.

  2. Step 2: Check each row for both words

    Row 1 has 'cat' and 'dog'. Row 2 has both words as well. Row 3 only has 'cat', missing 'dog'.

  3. Final Answer:

    [1, 2] -> Option B

  4. Quick Check:

    AND search returns rows with both terms [OK]
Hint: Use & in to_tsquery for AND condition [OK]
Common Mistakes:
  • Assuming OR instead of AND with &
  • Expecting rows missing one word to match
  • Confusing to_tsquery syntax
4.

Identify the error in this query:

SELECT * FROM posts WHERE content @@ 'cat | dog';
medium
A. content column cannot be used with @@ operator.
B. Incorrect use of the | operator inside the string.
C. The query should use LIKE instead of @@.
D. Missing to_tsquery() function around the search string.

Solution

  1. Step 1: Check the right side of @@ operator

    The right side must be a tsquery type, usually created by to_tsquery(). Here, it's a plain string.

  2. Step 2: Identify the missing function

    The query should be content @@ to_tsquery('cat | dog') to be valid.

  3. Final Answer:

    Missing to_tsquery() function around the search string. -> Option D

  4. Quick Check:

    Right side of @@ needs to_tsquery() [OK]
Hint: Always wrap search terms with to_tsquery() [OK]
Common Mistakes:
  • Using plain string without to_tsquery()
  • Thinking | operator is invalid inside to_tsquery
  • Confusing @@ with LIKE
5.

You want to find all rows in messages where the body contains either 'error' or 'failure' but not both. Which to_tsquery expression with @@ operator will achieve this?

hard
A. body @@ to_tsquery('(error | failure) & !(error & failure)')
B. body @@ to_tsquery('error | failure & !error & !failure')
C. body @@ to_tsquery('error <-> failure')
D. body @@ to_tsquery('error | failure')

Solution

  1. Step 1: Understand the requirement

    We want rows containing 'error' or 'failure' but NOT both. This is an exclusive OR (XOR) condition.

  2. Step 2: Analyze the options

    body @@ to_tsquery('(error | failure) & !(error & failure)') uses (error | failure) & !(error & failure), which means 'error or failure' AND NOT 'both error and failure'. This matches XOR logic.

  3. Step 3: Check other options

    body @@ to_tsquery('error | failure & !error & !failure') is invalid syntax. body @@ to_tsquery('error | failure') matches any with 'error' or 'failure' including both. body @@ to_tsquery('error <-> failure') looks for 'error' immediately followed by 'failure'.

  4. Final Answer:

    body @@ to_tsquery('(error | failure) & !(error & failure)') -> Option A

  5. Quick Check:

    XOR = (A OR B) AND NOT (A AND B) [OK]
Hint: Use (A | B) & !(A & B) for XOR in to_tsquery [OK]
Common Mistakes:
  • Using simple OR instead of XOR
  • Incorrect syntax for NOT operator
  • Confusing proximity operator <-> with XOR
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard