Infino
sign up talk to an engineer

agents / queries

A ranked result is a relation

Search is exposed as table-valued functions. Each returns your columns plus a score, so joins, aggregates, and window functions compose right on top of it.

infino help search

The search functions

  • Each function is an ordinary relation: put it in a FROM, a JOIN, a CTE, a subquery. Two of them can be joined against each other.
  • The k argument is the recall knob. Small k for a result page, large k when the search is feeding an aggregate.
  • Scalar predicates in the WHERE clause are pushed into the same pass, so filtering happens while the files are read.
  • hybrid_search('table', 'text_col', 'query', 'vec_col', :q, k)both retrievers, fused by RRF
  • bm25_search('table', 'col', 'query', k [, 'and' | 'or'])ranked keyword search
  • bm25_search_prefix('table', 'col', 'prefix', k)type-ahead, last token expanded
  • vector_search('table', 'vec_col', :q, k)approximate nearest neighbors
  • token_match('table', 'col', 'query' [, mode])unranked, every row that matches
  • exact_match('table', 'col', 'value')the raw string, no tokenizing

cat retrieve.sql # the page-one query

The full page-one query. The priority and recency filters run inside the retrieval pass.

retrieve.sql 
-- both retrievers, one pass over the bucket

SELECT   _id, subject, account_id, score
FROM     hybrid_search(
           'tickets',                               -- the table: files in your bucket
           'body', 'disk full on ingest',           -- the keyword side
           'embedding', :q, 200                     -- the vector side, 200 deep each
         )
WHERE    priority IN ('p0', 'p1')                   -- scalar filters ride the same pass,
  AND    created_at > now() - interval '30 days'    --   they are not applied afterwards
ORDER BY score DESC
LIMIT    20;

-- → ranked tickets with priority and recency filters applied

cat rollup.sql # joined to three plain tables

Three plain tables join onto the hits: accounts, plans, and a daily usage table. HAVING drops the groups too small to mean anything.

rollup.sql 
-- who is hitting this problem, on which plan, and how much do they ingest

WITH hits AS (
  SELECT _id, account_id, created_at, score
  FROM   hybrid_search('tickets', 'body', 'disk full on ingest',
                       'embedding', :q, 5000)     -- k is the recall knob
)
SELECT    p.name                          AS plan,
          a.region,
          count(*)                        AS tickets,
          count(DISTINCT h.account_id)    AS accounts,
          round(avg(h.score), 4)          AS relevance,   -- mean fused score
          sum(u.ingest_gb)                AS ingest_gb
FROM      hits h
JOIN      accounts a ON a.id = h.account_id            -- a plain table
JOIN      plans    p ON p.id = a.plan_id               -- and another
LEFT JOIN usage    u ON u.account_id = a.id
                        AND u.day = CAST(h.created_at AS DATE)
GROUP BY  p.name, a.region
HAVING    count(*) >= 10
ORDER BY  tickets DESC;

-- → matching tickets joined to accounts, plans, and usage, then grouped

cat trend.sql # windowed over time, still one query

The cohort split by region and smoothed: lag() for the week on week change, a frame clause for the moving average.

trend.sql 
-- is this problem growing, and where

WITH weekly AS (
  SELECT   a.region,
           date_trunc('week', h.created_at)  AS week,
           count(*)                          AS tickets,
           avg(h.score)                      AS relevance
  FROM     hybrid_search('tickets', 'body', 'disk full on ingest',
                         'embedding', :q, 20000) h
  JOIN     accounts a ON a.id = h.account_id
  WHERE    h.created_at > now() - interval '90 days'
  GROUP BY a.region, date_trunc('week', h.created_at)
)
SELECT   region,
         week,
         tickets,
         tickets - lag(tickets) OVER (PARTITION BY region ORDER BY week)
                                          AS wow_change,      -- week on week
         round(avg(tickets) OVER (PARTITION BY region ORDER BY week
                ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1)
                                          AS moving_avg_4w,   -- smoothed
         round(relevance, 4)              AS relevance
FROM     weekly
ORDER BY region, week;

-- → matching tickets grouped by week, with lag and moving average

One more: joining two search functions against each other →

Next: two retrievers, one ranking · one copy, searched in place · agent observability · back to RAG

Sign up Talk to an engineer