> ## Documentation Index
> Fetch the complete documentation index at: https://www.paradedb.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# External Indexes

> Combine ParadeDB search with filters backed by other database indexes

<Note>This feature is available on versions `0.25.5` and above.</Note>

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.

```sql theme={null}
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:

<CodeGroup>
  ```sql SQL theme={null}
  SELECT id, description
  FROM mock_items_geo
  WHERE description ||| 'running shoes'
    AND location <@ circle(point(50, 20), 4)
  ORDER BY id
  LIMIT 10;
  ```

  ```ts Drizzle theme={null}
  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);
  ```

  ```python Django theme={null}
  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]
  ```

  ```python SQLAlchemy theme={null}
  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()
  ```

  ```ruby Rails theme={null}
  MockItemGeo.search(:description)
             .match_any("running shoes")
             .where("location <@ circle(point(?, ?), ?)", 50, 20, 4)
             .order(:id)
             .limit(10)
             .select(:id, :description)
  ```

  ```cs EF Core theme={null}
  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();
  ```
</CodeGroup>

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

```sql theme={null}
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.

```sql theme={null}
CREATE INDEX geo_location_idx ON mock_items_geo
USING gist (location);
```

Run the same query again:

```sql theme={null}
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.

```text theme={null}
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](https://github.com/paradedb/paradedb/issues/new) with the query, index definitions, and `EXPLAIN` output.