Skip to content
Query.Farm
Talk with Us

Integrate with the optimizer

Make your table functions cooperate with DuckDB’s query optimizer — receive pushed-down WHERE predicates and report column statistics so the planner can skip work and move less data. Most relevant when your table functions back real data sources.

DuckDB plans the query; VGI supplies scan facts

DuckDB still owns planning and decides which filters/statistics matter. VGI gives your external table function a way to receive the predicates DuckDB pushed toward the scan, and a way to report statistics so DuckDB can plan as if it knew more about the external source.

Prerequisites

Section titled “Prerequisites”
  • A table or table-in-out function (see Function patterns).
  • Familiarity with your data’s shape (which columns are filterable, their ranges).

Filter pushdown

Section titled “Filter pushdown”

A table function can receive the WHERE predicates DuckDB would otherwise apply after the scan, and apply them at the source. Opt in with filter_pushdown = True in the function’s Meta. The framework deserializes the predicates for you and exposes them on params.current_pushdown_filters as a PushdownFilters tree (or None when no filter applies), refreshed before each process call:

class Events(TableFunctionGenerator[EventsArgs]):
  class Meta:
      filter_pushdown = True      # opt in to receiving WHERE predicates

  @classmethod
  def process(cls, params, state, out):
      filters = params.current_pushdown_filters   # PushdownFilters tree, or None
      # apply the filters while generating rows, then out.emit(...) / out.finish()

PushdownFilters is already decoded — you don’t call deserialize_filters yourself (that helper is for the raw wire bytes). To have the framework apply the filters to your output automatically, set auto_apply_filters = True in Meta. The node types and a worked example are in the Filter Pushdown reference and API: Filter Pushdown.

Column statistics

Section titled “Column statistics”

When a table reports per-column min/max, null, and distinct-count statistics, DuckDB’s optimizer can eliminate scans and order joins better. The declarative path is a statistics entry on the Table descriptor:

from vgi.catalog import Table
from vgi.catalog.descriptors import ColumnStatisticsInput

Table(
  name="departments",
  columns=schema,
  statistics={"id": ColumnStatisticsInput(min=1, max=10, has_null=False)},
)
-- With statistics the optimizer can prune an impossible predicate entirely:
EXPLAIN SELECT * FROM mydb.data.departments WHERE id > 100;   -- Physical Plan: EMPTY_RESULT

The snippets on this page are sketches — schema, EventsArgs and the departments table stand in for your own catalog. Full details — RPC-based dynamic statistics, TTLs, spatial bounds — are in the Column Statistics reference.

Requiring a filter instead of accepting one

Section titled “Requiring a filter instead of accepting one”

Pushdown is opportunistic — you take the predicates DuckDB happens to offer. When your upstream cannot answer without a key, invert it: declare required_filters on the table and the extension’s optimizer pass rejects any scan that doesn’t carry one, naming the unsatisfied groups. See Requiring filters on a scan.

Next steps

Section titled “Next steps”