--- title: Limitations description: Caveats for aggregate support canonical: https://www.paradedb.com/docs/documentation/aggregates/limitations --- ## ParadeDB Operator In order for ParadeDB to push down an aggregate, a ParadeDB text search operator must be present in the query. ```sql SQL -- 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(); ``` ```ts Drizzle 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))); ``` ```python Django 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() ``` ```python SQLAlchemy 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(), } ``` ```ruby Rails # 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 ``` ```cs EF Core // 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](/documentation/query-builder/compound/all) to the query's `WHERE` clause. ## Join Support Aggregate pushdown works across joins as well as single tables. When every participating table has a ParadeDB 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. ```sql SET paradedb.enable_aggregate_custom_scan TO on; ``` ### Supported shapes | Feature | Supported | | --- | --- | | Number of tables | Two or more (arbitrary join trees) | | Aggregate functions | `COUNT`, `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 BY` | Columns from any table in the join, including JSON sub-fields via `metadata->>'key'` | | `HAVING` clause | Comparisons against aggregate results and group columns | | Per-aggregate `FILTER (WHERE ...)` | Yes | | `ORDER BY ... LIMIT K` | Pushed down as TopK when there is a single `ORDER BY` column targeting an aggregate, a group column, or `MIN(col)` / `MAX(col)`, and no `pdb.agg()` in the query uses `terms` | | `ORDER BY` inside `STRING_AGG` / `ARRAY_AGG` | Yes (produces deterministic element ordering) | | `pdb.agg()` | `terms` (scalar or array fields; with `size`, `min_doc_count` of 1 or more, `missing`, and `order` by `_count`, `_key`, or a metric sub-aggregation, but not `include` or `exclude`), `sum`, `avg`, `min`, `max`, `value_count`, `cardinality`, and nested `aggs` built from these. The `solve_mvcc` argument is honored over a join as well. | ### `pdb.agg()` over joins A `pdb.agg()` field name must belong to exactly one indexed table in the join. When the same field name exists in several tables, qualify it with the table alias, as in `"field": "p.category"`. The qualifier is also accepted when the planner reduces the join to that one table. Postgres cannot see the fields inside a spec, so an outer join whose table is read only there is removed as unused and the spec fails with an unknown field; reference such a table outside the spec, or use an inner join. `pdb.agg()` over a NUMERIC field runs on this path as well, on a single table or over a join, while a `missing` value is not accepted for such a field. `terms` on an array field is also supported over joins; metric aggregations over array fields are not. A few `pdb.agg()` shapes run on a single table but not over a join: metric aggregations over JSON sub-fields, such as `{"sum": {"field": "metadata.qty"}}`, `terms` on a JSON sub-field that holds numbers, and `min_doc_count: 0`, which lists every term of the column. `pdb.agg()` has no native Postgres implementation, so it cannot fall back. A `pdb.agg()` over a join that uses an aggregation outside the list above, such as `range`, `histogram`, `date_histogram`, `filter`, `composite`, `stats`, `percentiles`, or `top_hits`, raises an error. ### Fallback to Postgres ParadeDB falls back to native Postgres execution when any of the following are true: - One or more tables in the join lacks a ParadeDB 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 When a fallback happens, the query still runs correctly through Postgres' native planner. ParadeDB simply does not accelerate it.