Skip to main content

​
ParadeDB Operator

In order for ParadeDB to push down an aggregate, a ParadeDB text search operator must be present in the query. 
-- Not pushed down
SELECT COUNT(id) FROM mock_items
WHERE rating = 5;

-- Pushed down
SELECT COUNT(id) FROM mock_items
WHERE rating = 5
AND id @@@ pdb.all();

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

// Not pushed down — no ParadeDB operator
await db
  .select({ count: count(mockItems.id) })
  .from(mockItems)
  .where(eq(mockItems.rating, 5));

// Pushed down — ParadeDB operator triggers aggregate pushdown
await db
  .select({ count: count(mockItems.id) })
  .from(mockItems)
  .where(and(eq(mockItems.rating, 5), search.all(mockItems.id)));

from paradedb import All, ParadeDB

# Not pushed down — no ParadeDB operator
MockItem.objects.filter(rating=5).count()

# Pushed down — ParadeDB operator triggers aggregate pushdown
MockItem.objects.filter(rating=5, id=ParadeDB(All())).count()

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

# Not pushed down.
count_without_operator_stmt = select(func.count(MockItem.id)).where(MockItem.rating == 5)

# Pushed down.
count_with_operator_stmt = select(func.count(MockItem.id)).where(
    MockItem.rating == 5,
    search.all(MockItem.id),
)

with Session(engine) as session:
    {
        "count_without_operator": session.execute(count_without_operator_stmt).scalar_one(),
        "count_with_operator": session.execute(count_with_operator_stmt).scalar_one(),
    }

# Not pushed down — no ParadeDB operator
MockItem.where(rating: 5).count

# Pushed down — ParadeDB operator triggers aggregate pushdown
MockItem.search(:id).match_all.where(rating: 5).count

// Not pushed down - no ParadeDB operator
await dbContext
    .MockItems.Where(item => item.Rating == 5)
    .CountAsync();

// Pushed down - ParadeDB operator triggers aggregate pushdown
await dbContext
    .MockItems.Where(item => item.Rating == 5 && EF.Functions.All(item.Id))
    .CountAsync();
If your query does not contain a ParadeDB operator, a way to “force” aggregate pushdown is to append the all query to the query’s WHERE clause.

​
Join Support

Aggregate pushdown works across joins as well as single tables. When every participating table has a BM25 index and the custom aggregate scan is enabled, ParadeDB computes the result directly from the index’s columnar storage, without scanning the underlying table rows. 
SET paradedb.enable_aggregate_custom_scan TO on;
The following join shapes are supported:
FeatureSupported
Join typesINNER, LEFT, RIGHT, FULL OUTER
Number of tablesTwo or more (arbitrary join trees)
Aggregate functionsCOUNT, COUNT(DISTINCT ...), SUM, SUM(DISTINCT ...), AVG, AVG(DISTINCT ...), MIN, MAX, STDDEV, STDDEV_POP, VARIANCE, VAR_POP, BOOL_AND, BOOL_OR, ARRAY_AGG, STRING_AGG
GROUP BYColumns from any table in the join, including JSON sub-fields via metadata->>'key'
HAVING clauseComparisons against aggregate results and group columns
Per-aggregate FILTER (WHERE ...)Yes
ORDER BY ... LIMIT KPushed down as TopK when there is a single ORDER BY column targeting an aggregate, a group column, or MIN(col) / MAX(col)
ORDER BY inside STRING_AGG / ARRAY_AGGYes (produces deterministic element ordering)
ParadeDB falls back to native Postgres execution when any of the following are true:
  • One or more tables in the join lacks a BM25 index
  • The join has no equality join condition (e.g. CROSS JOIN)
  • Join keys, GROUP BY columns, or aggregate arguments are not indexed columns
  • The query uses window functions (OVER ...), ROLLUP, CUBE, GROUPING SETS, LATERAL, or DISTINCT ON
  • GROUP BY uses a scalar function like date_trunc(...) or lower(...) (JSON sub-field access via ->> is supported)
  • The aggregate argument or result is wrapped in an expression such as COALESCE(SUM(...), 0) or a cast
  • The query uses pdb.agg() (use standard SQL aggregate functions instead)
When a fallback happens, the query still runs correctly through Postgres’ native planner. ParadeDB simply does not accelerate it.
Aggregate pushdown across joins is currently single-threaded. Parallel execution is on the roadmap.

​
NUMERIC Columns

NUMERIC columns do not support aggregate pushdown. Queries with aggregates on NUMERIC columns will automatically fall back to PostgreSQL for aggregation. For numeric data that requires aggregate pushdown, use FLOAT or DOUBLE PRECISION instead: 
-- Aggregates can be pushed down
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DOUBLE PRECISION
);

-- Aggregates fall back to PostgreSQL
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10,2)
);
Filter pushdown (equality and range queries) is fully supported for all NUMERIC columns. Only aggregate pushdown is not supported.