A query returns a relatively small number of rows.
It runs only every few seconds.
How bad could it be?
In a distributed database, potentially very bad if YugabyteDB has to scan and move a much larger set of candidate rows from DocDB toward the YSQL query layer before PostgreSQL can apply the remaining filters.
During a recent investigation, a broader network-traffic issue exceeded 100 TB/day across multiple clusters. Several queries contributed to the traffic. The investigation reinforced an important lesson: a query can return relatively few rows while a much larger candidate set is scanned, processed, or moved through the distributed system before filtering occurs.
The surprising part wasn’t the number of rows returned.
It was where the unwanted rows were being filtered out.
What Is Filter Pushdown?
YSQL query execution spans two important layers:
- ● the PostgreSQL-compatible YSQL query layer
- ● the distributed DocDB storage layer
Whenever possible, you want predicates evaluated as close to the data as possible.
Consider:
SELECT *
FROM orders
WHERE status = 'OPEN';
If DocDB can evaluate status = 'OPEN', rows that don’t match can be discarded before they are returned to the YSQL query layer.
That’s filter pushdown.
That distinction can be especially important in YugabyteDB because the rows needed by a query may reside on tablet servers running on other nodes.
But pushdown is only appropriate when the predicate can be evaluated in DocDB without changing PostgreSQL semantics. If evaluating the predicate requires session-specific context, YugabyteDB may intentionally leave the filter in the YSQL query layer.
The Execution Plan Tells the Story
One of the best tools for investigating this is:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT ...;
The DIST option adds distributed-storage runtime statistics such as storage read requests and rows scanned.
Pay close attention to where each predicate appears.
One subtle but important point:
- A sequential scan does not automatically mean the filtering is happening in PostgreSQL.
YugabyteDB can use a sequential scan and still show a Storage Filter, meaning DocDB is rejecting nonmatching rows before returning them to YSQL.
Next, let’s look at a case where YugabyteDB intentionally keeps a predicate in YSQL because pushing it into DocDB could change the query’s semantics.
Let’s Reproduce an Easy-to-Miss Case
Create a small test table:
DROP TABLE IF EXISTS pd_test;
CREATE TABLE pd_test (
id bigint,
createdstamp timestamptz NOT NULL,
payload bigint,
PRIMARY KEY (id HASH)
);
Load 100,000 rows with timestamps spread across approximately three months:
INSERT INTO pd_test
SELECT g,
timestamptz '2026-09-01 00:00:00+00'
+ (g % 90) * interval '1 day',
g
FROM generate_series(1, 100000) g;
ANALYZE pd_test;
There is intentionally no index on createdstamp. That lets us isolate the effect of filter pushdown without also changing the query’s access path.
Now let’s compare two otherwise similar predicates against the same timestamptz column:
- ● one using
timestampvalues - ● one using
timestamptzvalues
The difference looks subtle, but it determines where YugabyteDB can safely evaluate the predicate.
timestamp vs. timestamptz: Why the Filter Stays in YSQL
The SQL looks reasonable, but YugabyteDB intentionally keeps this predicate out of DocDB because evaluating it there could depend on time-zone context.
Our column is:
createdstamp timestamptz
Now run the query with values explicitly cast as timestamp without time zone:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT *
FROM pd_test
WHERE createdstamp >= '2026-10-01 00:00:00'::timestamp
AND createdstamp < '2026-11-01 00:00:00'::timestamp;
The actual plan is:
Seq Scan on pd_test (actual time=1.040..52.507 rows=34441 loops=1)
Filter: ((createdstamp >= '2026-10-01 00:00:00'::timestamp without time zone)
AND (createdstamp < '2026-11-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 65559
Storage Table Read Requests: 98
Storage Table Read Execution Time: 26.497 ms
Storage Table Read Ops: 98
Storage Table Rows Scanned: 100000
Planning Time: 0.056 ms
Execution Time: 59.258 ms
Storage Read Requests: 98
Storage Read Execution Time: 26.497 ms
Storage Read Ops: 98
Storage Rows Scanned: 100000
The numbers make the behavior easy to see:
The storage layer scanned all 100,000 rows, and the YSQL plan node then removed 65,559 of them.
Why Isn’t This Predicate Pushed Down?
This behavior is intentional.
The column is timestamptz, while the values in the query are timestamp without time zone. Comparing those types requires PostgreSQL to resolve the timestamp using time-zone context before it can be compared with the timestamptz.
YugabyteDB therefore keeps the comparison in the YSQL layer rather than pushing a time-zone-dependent conversion to distributed tablet servers, where the required context might not be equivalent.
timestamp with timestamptz requires a
time-zone conversion.
Fix the Type… and Watch the Filter Move
Now run the same date range, but explicitly use timestamptz values:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT *
FROM pd_test
WHERE createdstamp >= '2026-10-01 00:00:00+00'::timestamptz
AND createdstamp < '2026-11-01 00:00:00+00'::timestamptz;
This time, the plan changes:
Seq Scan on pd_test (actual time=2.545..59.741 rows=34441 loops=1)
Storage Filter: ((createdstamp >= '2026-10-01 00:00:00+00'::timestamp with time zone)
AND (createdstamp < '2026-11-01 00:00:00+00'::timestamp with time zone))
Storage Table Read Requests: 34
Storage Table Read Execution Time: 53.227 ms
Storage Table Read Ops: 34
Storage Table Rows Scanned: 100000
Planning Time: 10.596 ms
Execution Time: 66.872 ms
Storage Read Requests: 34
Storage Read Execution Time: 53.227 ms
Storage Read Ops: 34
Storage Rows Scanned: 100000
This time the predicate appears as a Storage Filter.
The explicit timestamptz values have already been resolved to exact points in time using the session’s time-zone context. DocDB therefore only needs to compare:
timestamptz → timestamptz
That comparison has the same meaning everywhere and can safely be evaluated in the distributed storage layer.
Before/After Comparison
createdstamp, so this remains a sequential scan.
The important change is that the 65,559 nonmatching rows are now rejected by the
DocDB Storage Filter instead of being rejected later by the
YSQL Filter.
Filter to
Storage Filter — not the elapsed time of these individual executions.
Recommended Practices for Filter Pushdown
The example above highlights a simple tuning goal:
- Eliminate unnecessary rows as early as possible, while preserving SQL correctness.
The goal is not to force every predicate into DocDB. The goal is to give YugabyteDB predicates and parameter types that can be evaluated correctly and as close to the data as possible.
1. Match Parameter Types to Column Semantics
If the column is:
createdstamp timestamptz
prefer parameters that are also timestamptz:
WHERE createdstamp >= $1::timestamptz
AND createdstamp < $2::timestamptz
or bind timezone-aware values using the appropriate PostgreSQL type from the application driver.
A timestamp without time zone does not represent an absolute point in time until a time zone is applied. When it is compared with a timestamptz, that conversion requires time-zone context.
Resolve the value to an unambiguous timestamptz first, and DocDB can safely compare equivalent types during distributed execution.
2. Cast the Parameter, Not the Column
When the problem is a parameter-type mismatch, prefer:
createdstamp >= $1::timestamptz
rather than transforming the table column:
createdstamp::timestamp >= $1
Keeping the column side of the predicate simple generally gives the optimizer more opportunity to use storage filtering and available indexes.
The broader rule is useful beyond timestamps:
- Prefer converting incoming values to the column’s intended type instead of wrapping the stored column in functions or casts.
3. Treat Driver and ORM Types as Part of Query Tuning
The SQL in your application source code is only part of the story.
Prepared statements, drivers, and ORMs can determine or alter the parameter types that YugabyteDB ultimately sees.
When a predicate behaves unexpectedly, check:
- ● the actual bound parameter types
- ● casts introduced by the driver or ORM
- ● timezone-aware versus timezone-naive date values
- ● whether prepared-statement parameter types match the target columns
A query can look perfectly reasonable in application code while reaching YugabyteDB with types that require additional PostgreSQL semantics before distributed execution is safe.
4. Verify Pushdown – Don’t Assume It
Use the execution plan to confirm where filtering actually happens:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT ...;
Pay particular attention to:
Index Cond:
Storage Filter:
Filter:
Rows Removed by Filter:
Storage Table Rows Scanned:
Storage Index Rows Scanned:
The SQL may look selective, but the plan tells you where that selectivity is actually being applied.
A Filter and a Storage Filter can represent very different amounts of distributed data movement even when the application receives the same final rows.
5. Compare Rows Scanned With Rows Returned
Don’t stop at:
rows=5000
Also look at statistics such as:
Storage Table Rows Scanned: 15000000
Those numbers answer different questions.
rows= tells you how many rows survived the plan node.
Storage ... Rows Scanned tells you how much storage work was required to produce them.
A useful tuning question is:
- How many rows did YugabyteDB scan, process, or discard before producing the final result?
A small result set does not necessarily mean a small amount of distributed work.
6. Optimize for Where Rows Are Discarded
The goal is not simply to make the final result set small.
The goal is to eliminate unwanted rows as early as correctness allows.
The important qualifier is “as early as correctness allows.”
Sometimes keeping a predicate in YSQL is exactly the correct behavior, as the timestamp / timestamptz example demonstrates.
7. Don’t Confuse Pushdown With Indexing
Filter pushdown and index access solve different problems.
In our timestamptz example, correcting the parameter type changed:
Filter
To:
Storage Filter
but both plans still scanned all 100,000 rows because there was no index on createdstamp.
A suitable index can address a different question:
- How many rows need to be examined in the first place?
The best execution plan may use both: selective index access to reduce how much data must be examined, and storage-layer filtering to reject remaining nonmatching rows before they reach the YSQL filter.
Why This Can Become a Network Problem
Now connect the pieces.
Imagine a large table distributed across multiple tablets and YB-TServers.
The client sends a query to one YB-TServer. Tablet servers that own the relevant data perform their reads.
If DocDB cannot safely evaluate an important predicate, more data may need to move toward the YSQL query layer before YSQL can reject the rows that do not qualify.
When the YSQL backend handling the query and the relevant tablet leaders are in different availability zones, that additional data movement can become billable cross-zone network traffic, depending on the cloud and deployment topology.
A query can therefore have all three of these characteristics at the same time:
- ● relatively low execution frequency
- ● a small result set
- ● substantial internal scanning and distributed data movement
That is why looking only at queries per second or rows returned to the application can hide the real problem.
Use EXPLAIN (ANALYZE, DIST) Before Guessing
For a suspicious SELECT, start with:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT ...;
Then compare the number of rows returned by the query with the distributed-storage work required to produce them:
rows=...
with distributed-storage statistics such as:
Storage Table Rows Scanned: ...
Storage Index Rows Scanned: ...
Storage Table Read Requests: ...
Storage Index Read Requests: ...
Also pay attention to where predicates appear:
Index Cond:
Storage Filter:
Filter:
Those lines can tell very different stories.
A large gap between rows scanned and rows ultimately returned deserves investigation, especially when many rows are being discarded by a YSQL Filter.
ANALYZE actually executes the statement and reports runtime statistics.
Be especially careful with expensive queries or statements that modify data.
EXPLAIN.
A Practical Filter-Pushdown Checklist
When a query returns relatively few rows but appears to be doing far too much work, check:
- 1. Does the plan show
Filter,Storage Filter, orIndex Cond? - 2. How many storage rows were scanned versus rows returned?
- 3. Are large numbers of rows being removed by a YSQL
Filter? - 4. Could one or more predicates become part of an index access condition?
- 5. Are functions or casts being applied to important filter columns?
- 6. Do application parameter types match the intended column semantics?
- 7. For
timestamptz, has the incoming value already been resolved to an unambiguous point in time? - 8. Could a more selective index reduce how many rows must be examined?
- 9. How frequently does the query execute?
Related YugabyteDB Tips
This topic fits nicely with two earlier YugabyteDB Tips that focus on reducing unnecessary query work.
Those Tips focus primarily on index efficiency.
This Tip adds another important question for distributed query execution:
- Were unwanted rows eliminated in DocDB, or did they continue toward the YSQL query layer before being filtered?
Final Takeaway
The number of rows returned by a query is only the end of the story.
In YugabyteDB, you also want to understand:
- ● how many rows were scanned
- ● where predicates were evaluated
- ● how many rows were discarded
- ● whether those rows had to move between distributed components first
Filter, Storage Filter, and
Index Cond can tell very different stories.
In a distributed database, query tuning is not only about reducing execution time.
Sometimes understanding where your filters are actually evaluated can help you avoid a very real network bill.
Have Fun!
My wife and I stopped by the prairie dog exhibit at the Smithsonian’s National Zoo, where a bunch were running around and seemingly playing. But this little group caught my eye… off to the side, eating together and watching the chaos.
Clearly, we’d found the “let’s just have snacks and people-watch” crowd. My kind of prairie dogs. 😂