Query pushdown

What is query pushdown?

Federated engines such as Trino read from many sources through connectors. Trino’s documentation lists predicate, projection, dereference, aggregation, join, limit and top-N pushdown. With predicate pushdown, for example, the connector hands the WHERE clause to the data source, which evaluates it there.¹Source 1 · Trino DocumentationPushdowntrino.io PostgreSQL’s foreign data wrapper does the same between databases: postgres_fdw sends WHERE clauses to the remote server and skips columns the query does not need, to reduce the data transferred.²Source 2 · PostgreSQL Global Development Grouppostgres_fdw: access data stored in external PostgreSQL serverspostgresql.org

Example: what actually gets sent

12345678-- Question: revenue by plan for the last 30 days
-- Without pushdown: SELECT * FROM invoices;  -- every row and column travels

-- With predicate, projection and aggregation pushdown:
SELECT plan, SUM(amount) AS revenue
FROM invoices
WHERE paid_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY plan;

On a table with millions of invoices, the second form returns a handful of rows. The result is the same either way; only where the work happens changes.

Common misconceptions

• “The whole query is always pushed.” Pushdown is per operation. By default, postgres_fdw only sends WHERE clauses that use built-in (and immutable) operators and functions; others are checked locally after the rows are fetched.²Source 2 · PostgreSQL Global Development Grouppostgres_fdw: access data stored in external PostgreSQL serverspostgresql.org
• “Pushdown is free.” It moves the work onto the source. Point heavy analytical queries at a read replica, and set rate limits.
• “If the plan looks fast, it was pushed.” Verify it. In Trino, when a predicate is pushed down, the EXPLAIN plan no longer shows a ScanFilterProject operation for it.¹Source 1 · Trino DocumentationPushdowntrino.io With postgres_fdw, EXPLAIN VERBOSE prints the exact remote SQL.²Source 2 · PostgreSQL Global Development Grouppostgres_fdw: access data stored in external PostgreSQL serverspostgresql.org

How Kimo uses query pushdown

Kimo’s semantic layer compiles every dashboard tile and Ask Kimo question into SQL for the source’s dialect. In Bridge mode, Kimo Bridge runs that SQL on your server under a read-only role and returns only the aggregated result over an outbound-only tunnel. Kimo keeps no copy of your rows, apart from optional short-lived result caches you can switch off. Learn more in the Bridge security model and Cloud, hybrid or Bridge?.

Related terms

• Outbound-only tunnel: how pushed-down queries reach a private database.
• ELT vs ETL: the copy-first alternatives.
• Data residency: why keeping rows at the source can matter.