External Indexes - ParadeDB

This feature is available on versions 0.25.5 and above.

Some filters are best handled by a regular database index instead of by the ParadeDB index. This is common when the filter depends on an extension type or a search method built for a specialized domain. Examples include ltree path filters or PostGIS spatial filters. When a useful external index is available, ParadeDB can use it to narrow the candidate rows before returning search results. Without that external index, ParadeDB still returns correct results, but it checks the specialized filter row by row. As an example, let’s start with the built-in mock_items table and create a larger example table with one specialized location column.

DROP TABLE IF EXISTS mock_items_geo;

CREATE TABLE mock_items_geo AS
SELECT
  row_number() OVER (ORDER BY mock_items.id, sample_id) AS id,
  mock_items.description || ' ' || sample_id AS description,
  mock_items.rating,
  mock_items.category,
  point(sample_id % 100, sample_id / 100) AS location
FROM mock_items
CROSS JOIN generate_series(1, 2500) AS sample_id;

CREATE INDEX geo_search_idx ON mock_items_geo
USING paradedb (id, description, category)
WITH (key_field = 'id');

Next, run a query that combines a ParadeDB text search with a geometric filter:

SELECT id, description
FROM mock_items_geo
WHERE description ||| 'running shoes'
  AND location <@ circle(point(50, 20), 4)
ORDER BY id
LIMIT 10;

import { and, sql } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    id: mockItemsGeo.id,
    description: mockItemsGeo.description,
  })
  .from(mockItemsGeo)
  .where(
    and(
      search.matchAny(mockItemsGeo.description, "running shoes"),
      sql`${mockItemsGeo.location} <@ circle(point(50, 20), 4)`,
    ),
  )
  .orderBy(mockItemsGeo.id)
  .limit(10);

from paradedb import MatchAny, ParadeDB

MockItemGeo.objects.filter(
    description=ParadeDB(MatchAny("running shoes")),
).extra(
    where=["location <@ circle(point(%s, %s), %s)"],
    params=[50, 20, 4],
).order_by("id").values("id", "description")[:10]

from sqlalchemy import select, text
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search

stmt = (
    select(MockItemGeo.id, MockItemGeo.description)
    .where(
        search.match_any(MockItemGeo.description, "running shoes"),
        text("location <@ circle(point(:x, :y), :radius)"),
    )
    .order_by(MockItemGeo.id)
    .limit(10)
)

with Session(engine) as session:
    session.execute(stmt, {"x": 50, "y": 20, "radius": 4}).all()

MockItemGeo.search(:description)
           .match_any("running shoes")
           .where("location <@ circle(point(?, ?), ?)", 50, 20, 4)
           .order(:id)
           .limit(10)
           .select(:id, :description)

var x = 50;
var y = 20;
var radius = 4;

await dbContext
    .MockItemsGeo.FromSqlInterpolated($"""
        SELECT *
        FROM mock_items_geo
        WHERE description ||| {"running shoes"}
          AND location <@ circle(point({x}, {y}), {radius})
        """)
    .OrderBy(item => item.Id)
    .Take(10)
    .Select(item => new { item.Id, item.Description })
    .ToListAsync();

To inspect the plan before adding a separate index on location, run:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, description
FROM mock_items_geo
WHERE description ||| 'running shoes'
  AND location <@ circle(point(50, 20), 4)
ORDER BY id LIMIT 10;

Without a separate index on location, ParadeDB finds the text matches first and then checks the location filter row by row. Now add an index on location that can answer this kind of containment filter.

CREATE INDEX geo_location_idx ON mock_items_geo
USING gist (location);

Run the same query again:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, description
FROM mock_items_geo
WHERE description ||| 'running shoes'
  AND location <@ circle(point(50, 20), 4)
ORDER BY id LIMIT 10;

The plan should have changed to include a Bitmap Index Scan. This means the location filter is no longer being checked row by row. Over large result sets, this results in a significant reduction in page reads and, consequently, query time improvement.

Custom Scan (ParadeDB Base Scan) on mock_items_geo
  Index: geo_search_idx
  ...
  ->  Bitmap Index Scan on geo_location_idx
        Index Cond: (location <@ '<(50,20),4>'::circle)

Limitations

This is an optimization, so ParadeDB will fall back to regular row-by-row filtering when the safer or cheaper path is to avoid the extra index. The most common reasons are:

The filter appears under OR or NOT. Only AND is currently supported.
More than one external index could help. ParadeDB currently chooses the best single external index instead of combining several.
The filter uses SQL’s = ANY (...) array form.
The candidate set from the external index is expected to be too large, or the filter is not selective enough to justify the extra work.

If your query still checks the filter row by row, please open a GitHub issue with the query, index definitions, and EXPLAIN output.

​Limitations

Limitations