> ## 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. # Limitations > Caveats for aggregate support ## ParadeDB Predicate In order for ParadeDB to push down an aggregate, a ParadeDB predicate must be present in the query. ```sql SQL theme={null} -- 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 theme={null} 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 theme={null} 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 theme={null} 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 theme={null} # 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 theme={null} // 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](/docs/reference/full-text/all) to the query's `WHERE` clause. ## Date Grouping `GROUP BY DATE(created_at)` and `GROUP BY created_at::date` support aggregate pushdown when `created_at` is a bare `timestamp without time zone` column that is [columnar indexed](/docs/reference/indexing/columnar). The usual aggregate pushdown requirements still apply, including a ParadeDB operator in the query. The result matches Postgres for the full timestamp range, including `infinity`, `-infinity` and `NULL`. Date grouping can be combined with other grouping columns and per-aggregate `FILTER` clauses. The following forms still fall back to native Postgres execution: * `DATE(timestamptz_column)`, because the result depends on the session `TimeZone`. * `DATE(text_column::timestamp)` and other non-bare timestamp expressions. * Other scalar grouping functions, such as `date_trunc(...)` and `lower(...)`. ## Join Support Aggregate pushdown works across joins as well as single tables. When every participating table has a ParadeDB index, ParadeDB computes the result directly from the index's columnar storage, without scanning the underlying table rows. The custom aggregate scan is enabled by default; to turn it off, run `SET paradedb.enable_aggregate_custom_scan TO off;`. ### 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'` and the supported `DATE(timestamp)` form described above | | `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 `visibility` argument is honored; over a join, `threshold` makes one decision for every table, judged on the largest table's estimate. | ### `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 For aggregates over joins, 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`. The single-table [faceted query](/docs/reference/aggregates/facets) form `pdb.agg(...) OVER ()` is pushed down, but not across joins * `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.