Recommended SQL shapes

The examples on this page use two tables. posts has TIN indexes on title and body and btree indexes on author_id and created_at. authors has a TIN index on bio.

CREATE TABLE authors (
  id     bigint PRIMARY KEY,
  name   text NOT NULL,
  bio    text NOT NULL,
  topics text NOT NULL
);

CREATE INDEX authors_bio_tin ON authors USING tin (bio);

CREATE TABLE posts (
  id         bigint PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES authors (id),
  title      text NOT NULL,
  body       text NOT NULL,
  ups        integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX posts_title_tin ON posts USING tin (title);
CREATE INDEX posts_body_tin ON posts USING tin (body);
CREATE INDEX posts_author_id ON posts (author_id);
CREATE INDEX posts_created_at ON posts (created_at);

Ranked results

-- $1 is the TINQL query, $2 the page size
SELECT id, title, tin.score(ctid) AS score
FROM posts
WHERE body ==> $1
ORDER BY score DESC
LIMIT $2;

ORDER BY tin.score(ctid) DESC LIMIT k is the shape TIN is built around. It returns the k best rows in score order and stops as soon as it has them. Passing the query as a parameter keeps user input out of the SQL text.

SELECT id, title,
       tin.score(ctid) AS score,
       tin.score(ctid) / tin.max_score(ctid) AS relative
FROM posts
WHERE body ==> $1
ORDER BY score DESC
LIMIT $2;

tin.max_score(ctid) is the best score among the matches and has the same value on every row, so dividing by it gives a relative score between 0 and 1. That is the number to show in a relevance bar, or to compare against a threshold when you want to hide weak matches.

Filters and counts

SELECT id, title
FROM posts
WHERE body ==> '"fuji apple"';

SELECT count(*)
FROM posts
WHERE body ==> 'error OR fail';

Without tin.score, ==> is an ordinary boolean filter. A count(*) over a ==> predicate is answered from the index.

Several TIN-indexed columns

SELECT id, title, tin.score(ctid) AS score
FROM posts
WHERE title ==> 'espresso^2'
  AND body ==> 'grinder OR "burr grinder"'
ORDER BY score DESC
LIMIT 10;

Each TIN index covers one column, so a query over several columns uses one ==> per column. tin.score(ctid) adds up the relevance from every column that matched. The boost on the title query makes a title hit count twice as much as a body hit, which is the usual way to say that one field matters more than another.

SELECT id, title, tin.score(ctid) AS score
FROM posts
WHERE title ==> 'espresso' OR body ==> 'espresso'
ORDER BY score DESC
LIMIT 10;

With OR, a row qualifies when either column matches, and its score sums whatever matched. This is the shape for a single search box that should look in several fields.

Filtering on an unindexed column

SELECT id, title, tin.score(ctid) AS score
FROM posts
WHERE body ==> 'espresso'
  AND ups > 100
ORDER BY score DESC
LIMIT 10;

ups has no index. TIN finds the rows that match the text and the ups condition is checked on each of them, so it costs one comparison per candidate and needs no index of its own. When the filter rejects most of the text matches, the search has to look further down the ranking to fill the limit, so this shape suits filters that keep a reasonable share of the matches.

Combining with btree indexes

-- Point lookup
SELECT id, title
FROM posts
WHERE body ==> 'espresso'
  AND author_id = 42;

-- Range
SELECT id, title, tin.score(ctid) AS score
FROM posts
WHERE body ==> 'espresso'
  AND created_at >= now() - interval '7 days'
ORDER BY score DESC
LIMIT 10;

When the other condition has a btree index, TIN can use both indexes. A selective point or range condition narrows the text search rather than filtering its results afterwards, which matters when the text query matches many rows and the other condition matches few. A recency window over a large corpus is the common case.

SELECT id, title
FROM posts
WHERE body ==> '"espresso machine"'
   OR id = 21462;

With OR, each index contributes its own rows and the result is the union without duplicates. An OR costs about what its more expensive side costs, so a cheap phrase plus a primary-key lookup stays cheap.

Joins with a score on each side

SELECT p.id, p.title,
       tin.score(p.ctid) AS post_score,
       tin.score(a.ctid) AS author_score
FROM posts p
JOIN authors a ON a.id = p.author_id
WHERE p.body ==> 'espresso'
  AND a.bio ==> 'barista OR roaster'
ORDER BY post_score + author_score DESC
LIMIT 10;

tin.score takes the ctid of the relation it scores, so a join can carry one score per side. Each side needs its own ==> predicate. A side with no text predicate has nothing to score, and the query is refused. The two scores are independent BM25 values from two different indexes, so combine them deliberately: add them, weight one, or order by one and display the other.

SELECT a.name, p.id, p.title, p.score
FROM authors a
CROSS JOIN LATERAL (
  SELECT id, title, tin.score(ctid) AS score
  FROM posts
  WHERE body ==> a.topics
  ORDER BY score DESC
  LIMIT 3
) p
ORDER BY a.name, p.score DESC;

The right-hand side of ==> can be a column from the outer query, so here the search is bound once per author using that author’s topics as the TINQL query. A LATERAL subquery with ORDER BY tin.score(ctid) DESC LIMIT k returns the top k for each outer row. This is the shape behind “recommended for you” lists and any page that shows the best matches per user, per category, or per saved search.

Need help?

Get help from the PlanetScale Support team, or join our Discord community to see how others are using PlanetScale.

Onboarding

Platform

Guides

Recommended SQL shapes

Ranked results

Filters and counts

Several TIN-indexed columns

Filtering on an unindexed column

Combining with btree indexes

Joins with a score on each side

Need help?

​Ranked results

​Filters and counts

​Several TIN-indexed columns

​Filtering on an unindexed column

​Combining with btree indexes

​Joins with a score on each side

​Need help?

Ranked results

Filters and counts

Several TIN-indexed columns

Filtering on an unindexed column

Combining with btree indexes

Joins with a score on each side

Need help?