Overview

​

The @@@ Operator

While the search function is useful for simple full text search queries, it is not optimized for

• Returning hundreds of thousands or millions of documents as quickly as possible
• Usage in complex SQL queries with other “plain SQL” WHERE or JOIN clauses

For more complex queries, we recommend using the @@@ operator. The @@@ operator means “find all rows matching a full text query.” It’s designed to return large numbers of rows as quickly as possible and interacts well with ORDER BY, OFFSET/LIMIT, and GROUP BY clauses as well as standard query operators such as AND, OR, and NOT.

@@@ itself does not perform any ordering, offsets or limits. These are now operations that need to be added to the overall query with ORDER BY, OFFSET, and LIMIT. @@@ also does not return scores. If scores are required, score_bm25 should be used.

​

Basic Usage

The left-hand side of @@@ must be the key field. The right-hand side accepts either a full text query string or a query builder function.

For instance, the following full text queries will return the same rows, but not necessarily in the same order.

-- search() syntax
SELECT * FROM search_idx.search('description:shoes');

-- @@@ operator syntax
SELECT * FROM mock_items
WHERE id @@@ 'description:shoes';

Similarly, the following query builder functions will return the same rows.

-- search() syntax
SELECT * FROM search_idx.search(
    query => paradedb.term('description', 'shoes')
);

-- @@@ operator syntax
SELECT * FROM mock_items
WHERE id @@@ paradedb.term('description', 'shoes');