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
orJOIN
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.
We are undergoing an internal refactor to move more of the API away from the <index_name>.search
syntax and towards the @@@
operator, which delivers a number of performance and
syntax improvements. Over time, we expect to more heavily recommend the @@@
operator.
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');
Was this page helpful?