When Bad Queries Turn Into Big Bills: Filter Pushdown in YugabyteDB

Jim Knicely

A query returns only a few thousand 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 rows from DocDB to the YSQL query layer before PostgreSQL can apply the filters.

During a recent investigation, a broader network-traffic issue exceeded 100 TB/day across multiple clusters. Several queries contributed to the traffic. One particularly interesting query ran only about once every eight seconds and returned roughly 1,600–6,000 rows per execution, yet it was estimated to walk around 15 million entries.

💰 Why This Matters: Network Traffic Has a Real Cost
In the broader investigation that inspired this Tip, cross-zone network charges increased from roughly $30–$60 per day to about $1,050 per day.
The associated traffic was approximately 117 TB per day. If sustained, that represented roughly $31,000 per month in additional network charges.
Several queries contributed to that traffic, so those totals should not be attributed to the single query demonstrated in this Tip. The broader lesson is: inefficient distributed query execution can become a billing problem surprisingly quickly.

The surprising part wasn’t the number of rows returned.

It was where the unwanted rows were being filtered out.

⚠️ Small Result Set ≠ Small Amount of Work
A query returning 6,000 rows does not necessarily mean YugabyteDB read or transferred only 6,000 rows.
If a predicate cannot be pushed into the distributed storage layer, YugabyteDB may retrieve a much larger candidate set and discard rows later in the YSQL query layer.

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.

❌ Filter in YSQL
DocDB scans candidate rows
↓
Rows travel toward YSQL
↓
PostgreSQL applies the filter
↓
Many rows may be discarded
✅ Filter Pushed Down
DocDB scans candidate rows
↓
DocDB applies the filter
↓
Only qualifying rows travel upward
↓
YSQL receives less data

That distinction can be especially important in YugabyteDB because the rows needed by a query may reside on tablet servers running on other nodes.

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.

Plan Element What It Tells You
Index Cond The predicate is helping determine what portion of an index is accessed.
Storage Filter The storage layer evaluates the predicate before returning qualifying rows to YSQL.
Filter PostgreSQL evaluates the predicate at that YSQL plan node rather than as a storage-layer filter.
Rows Removed by Filter Candidate rows were rejected by the PostgreSQL filter at that plan node.
Storage Rows Scanned Shows how many rows were scanned by the distributed storage layer.

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. The YugabyteDB documentation includes examples of exactly that behavior.

💡 Don’t Stop at “Seq Scan”
A sequential scan may still benefit from storage-layer filtering.
Look at where the predicate is evaluated, how many storage rows were scanned, and how many rows ultimately survived.

Let’s Reproduce Two Easy-to-Miss Cases

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;

Now let’s look at two predicates that appear reasonable but behave very differently from what you might expect.

Scenario 1: yb_hash_code(id) % N

Suppose an application wants to split a table into several buckets for parallel processing:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT *
FROM pd_test
WHERE yb_hash_code(id) % 3 = 0;

The actual plan from this reproduction is:

				
					Seq Scan on pd_test (actual time=0.908..51.748 rows=33259 loops=1)
  Filter: ((yb_hash_code(id) % 3) = 0)
  Rows Removed by Filter: 66741
  Storage Table Read Requests: 98
  Storage Table Read Execution Time: 19.756 ms
  Storage Table Read Ops: 98
  Storage Table Rows Scanned: 100000
Planning Time: 4.671 ms
Execution Time: 58.467 ms
Storage Read Requests: 98
Storage Read Execution Time: 19.756 ms
Storage Read Ops: 98
Storage Rows Scanned: 100000

The numbers make the problem easy to see:

100,000
Storage Rows Scanned
66,741
Rows Removed by Filter
33,259
Rows Returned

The key line is:

				
					Filter: ((yb_hash_code(id) % 3) = 0)

rather than:

				
					Storage Filter:

The storage layer scanned all 100,000 rows, and PostgreSQL then discarded 66,741 of them at the YSQL plan node.

That means about two-thirds of the scanned rows were rejected only after the storage work had already happened.

⚠️ This Is Exactly What We’re Looking For
The query returned only 33,259 rows, but DocDB scanned 100,000 rows.
The Rows Removed by Filter: 66741 line shows that those rows were rejected by the YSQL filter rather than by a pushed-down storage filter.

Scenario 2: timestamp vs. timestamptz

The first example was caused by the shape of the expression.

The second is more subtle: the SQL looks reasonable, but YugabyteDB intentionally keeps the predicate out of DocDB when 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 are easy to see:

100,000
Storage Rows Scanned
65,559
Rows Removed by YSQL Filter
34,441
Rows Returned

The key line is again:

				
					Filter:

rather than:

				
					Storage Filter:

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 a timestamptz, but the literals in this query are timestamp without time zone.

Comparing those two types requires PostgreSQL to interpret the timestamp using a time zone before it can be compared with the timestamptz.

In a distributed database, that matters. YugabyteDB should not push a time-zone-dependent conversion down to tablet servers if that conversion could depend on local time-zone context and potentially produce different results on different servers.

💡 This Is a Correctness Decision, Not a Pushdown Bug
Comparing timestamp with timestamptz requires a time-zone conversion.
YugabyteDB intentionally keeps that comparison in the YSQL layer rather than allowing distributed tablet servers to perform a conversion that could depend on time-zone context.

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

And there it is:

				
					Storage Filter:

instead of:

				
					Filter:

The explicit timestamptz value is resolved to an exact point in time using the session’s time-zone context before the predicate is pushed down.

At that point, DocDB only needs to compare:

  • timestamptz → timestamptz

That comparison has the same meaning everywhere, so it can safely be evaluated by the distributed storage layer.

Before/After Comparison
Metric ::timestamp ::timestamptz
Filter Location Filter Storage Filter
Storage Rows Scanned 100,000 100,000
Rows Removed by YSQL Filter 65,559 None reported
Rows Returned 34,441 34,441
Storage Read Requests (this run) 98 34
💡 Pushdown Does Not Necessarily Mean Fewer Rows Scanned
Notice that both plans still scanned all 100,000 rows.
There is no index on createdstamp, so this remains a sequential scan. The improvement is that the 65,559 nonmatching rows are now rejected by the DocDB Storage Filter instead of being rejected later by the YSQL Filter.

The difference can be visualized like this:

❌ ::timestamp
Scan 100,000 Rows
↓
Rows Reach the YSQL Filter
↓
YSQL Removes 65,559 Rows
✅ ::timestamptz
Scan 100,000 Rows
↓
DocDB Applies the Storage Filter
↓
Only 34,441 Qualifying Rows Continue
⚠️ Don’t Benchmark These Two Execution Times
In this small 100,000-row test, the pushed-down query happened to take slightly longer. Planning time, caching, catalog activity, storage timing, and normal run-to-run variation can easily dominate a test this small.
The important result is the change from Filter to Storage Filter… not the elapsed time of these individual executions.

The important takeaway is that this is not a pushdown bug. YugabyteDB intentionally avoids pushing down a comparison whose result would depend on a time-zone conversion. Once the value is resolved to an unambiguous timestamptz, the comparison has the same meaning everywhere and can safely be evaluated by DocDB.

Recommended Workarounds and Best Practices

The two scenarios above had different causes, but the tuning goal is the same:

  • Eliminate unnecessary rows as early as possible, while preserving SQL correctness.

Sometimes that means changing the predicate. Sometimes it means fixing a parameter type. And sometimes the next step is giving YugabyteDB a more selective access path.

1. Prefer Pushdown-Friendly Hash Ranges

If you’re dividing a hash-partitioned table into chunks for multiple workers, prefer contiguous hash ranges such as:

				
					WHERE yb_hash_code(id) >= $1
  AND yb_hash_code(id) <  $2

rather than expressions such as:

				
					WHERE yb_hash_code(id) % 16 = 0

when your workload can be partitioned that way.

The range form gives YugabyteDB a predicate that can be evaluated earlier and can help restrict the portion of the hash space that needs to be accessed.

For pushdown to take advantage of YugabyteDB’s physical hash partitioning, the arguments to yb_hash_code() must represent the full hash key of the table or index being accessed. In this demo, id is the complete hash key, so yb_hash_code(id) qualifies.

💡 Parallelism Can Multiply the Problem
Imagine dividing a table into 16 modulo-based buckets and running one worker for each bucket.
If the modulo predicate cannot restrict which tablets are accessed, each worker may still scan across the distributed table. One logical pass through 16 buckets can therefore turn into many large distributed scans.
Contiguous yb_hash_code() ranges are different: YugabyteDB can use the requested hash range to limit work to the tablets whose hash ranges overlap that worker’s assignment.

2. Resolve Time Zones Before Pushdown

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 does not identify an absolute point in time until a time zone is applied. YugabyteDB intentionally avoids pushing that conversion down when doing so could make evaluation depend on time-zone context.

Resolve the value to an unambiguous timestamptz first, and DocDB can safely compare timestamptz values everywhere.

3. Cast the Parameter, Not the Column

When the problem is a parameter-type mismatch, prefer:

				
					createdstamp >= $1::timestamptz

instead of transforming the table column:

				
					createdstamp::timestamp >= $1

Keeping the column side of the predicate simple generally gives the optimizer more opportunity to use storage pushdown and available indexes.

⚠️ Be Careful With Functions and Casts on Filter Columns
Wrapping an important filter column in a function, cast, or expression can change what the optimizer and storage layer are able to do with that predicate.
When possible, keep the column side simple and transform the parameter instead.

4. Verify Pushdown… Don’t Assume It

Use the execution plan to confirm where filtering actually happens:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT ...;

Look for:

  • ● Index Cond
  • ● Storage Filter
  • ● Filter
  • ● Rows Removed by Filter
  • ● Storage Table Rows Scanned
  • ● Storage Index Rows Scanned
  • ● storage read requests

The SQL may look selective, but the execution plan tells you where that selectivity is actually being applied.

5. Compare Rows Scanned With Rows Returned

Suppose the plan returns:

				
					rows=5000

but also reports:

				
					Storage Table Rows Scanned: 15000000

That deserves attention.

The rows= value tells you what survived.

It does not tell you how much work was required to produce those rows.

A better tuning question is:

  • How many rows did YugabyteDB scan, process, or discard before producing the final result?

6. Treat Driver and ORM Types as Part of Query Tuning

Prepared statements can hide important details.

When a plan behaves unexpectedly, check:

  • ● the actual bound parameter types
  • ● casts introduced by the driver
  • ● casts introduced by an ORM
  • ● timezone-aware versus timezone-naive date values
  • ● whether the prepared statement’s parameter types match the table definition
💡 The SQL Text Is Only Part of the Story
What matters to the optimizer is the statement YugabyteDB actually receives, including the parameter types supplied by the client.

7. Optimize for Where Rows Are Discarded

The goal is not simply to make the final result set small.

The goal is to reject unwanted rows as early as possible.

✅ Better: Eliminate Rows Early
Efficient Access Path
↓
DocDB Storage Filtering
↓
Fewer Rows Reach YSQL
↓
Application Gets the Result
❌ Avoid: Eliminate Rows Late
Large Storage Scan
↓
More Data Moves Toward YSQL
↓
YSQL Applies the Filter
↓
Small Result Set Survives

8. Don’t Confuse Pushdown With Indexing

The timestamptz example demonstrated an important distinction.

After fixing the parameter type:

  • ● the query still scanned 100,000 rows
  • ● the same 34,441 rows were returned
  • ● but filtering moved from the YSQL Filter to the DocDB Storage Filter

Pushdown changed where unwanted rows were eliminated.

It did not make the sequential scan disappear.

A suitable index can address a different problem: how many rows need to be examined in the first place.

And for hash-partitioned workloads, a pushdown-friendly yb_hash_code() range can address yet another problem: which tablets need to participate at all.

Hash-Range Pruning
Can reduce which tablets need to participate.
Selective Index Access
Can reduce how many rows need to be examined.
Storage Filter
Determines where remaining nonmatching rows are rejected.

The best execution plan may benefit from all three: hash-range pruning to avoid contacting unnecessary tablets, 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.

💡 The Goal: Contact Less, Scan Less, Filter Earlier
Don’t optimize only for the number of rows returned to the application. Pay attention to how many tablets participate, how many rows are scanned, and where unwanted rows are ultimately discarded.
In a distributed database, improving all three can reduce storage work, distributed RPCs, network traffic, and query latency.

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 apply an important predicate, more data may need to move toward the YSQL query layer before PostgreSQL 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 unnecessary data movement can also become billable cross-zone network traffic.

Distributed Storage
Tablet Servers Scan Candidate Rows
↓
Distributed Data Movement
Candidate Data Moves Toward the YSQL Query Layer
↓
YSQL
PostgreSQL Applies the Remaining Filter
↓
Only the Final Result Set Survives

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.

⚠️ Low Query Volume Does Not Guarantee Low Resource Usage
A query running only once every several seconds can still generate a large amount of storage work and network activity if each execution scans and moves enough data.

Use EXPLAIN (ANALYZE, DIST) Before Guessing

For a suspicious SELECT, start with:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT ...;

Then compare the number of rows ultimately returned:

				
					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 query that returns 5,000 rows after scanning 15 million deserves investigation even if it runs relatively infrequently.

⚠️ Remember: EXPLAIN ANALYZE Executes the Query
ANALYZE actually executes the statement and reports runtime statistics. Be especially careful with expensive queries or statements that modify data.
If you only need the optimizer’s estimated execution plan, start with plain 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, or Index 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, casts, or expressions wrapped around important filter columns?
  •   6. Do application parameter types match the intended column semantics?
  •   7. For timestamptz, has the value already been resolved to an unambiguous point in time?
  •   8. Is yb_hash_code() being used as a contiguous range or wrapped inside another expression?
  •   9. Could a more selective index reduce how many rows must be examined?
  • 10. How frequently does the query execute?
💡 Don’t Stop at “Use an Index”
Indexing is only one part of the story.
A better question is: Where is each predicate actually evaluated, and how much work has already happened before the unwanted rows are discarded?

Related YugabyteDB Tips

This topic fits nicely with two earlier YugabyteDB Tips that focus on reducing unnecessary query work.

🔗 Continue the Query-Tuning Journey
See how adding the right filter columns to an index can eliminate wasted filtering work.
See why using an index does not necessarily mean a query reads only the rows it ultimately returns.

Those Tips focus primarily on index efficiency.

This Tip adds another distributed-system question:

  • 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
✅ Key Takeaway
🔎 Read the Plan
Filter, Storage Filter, and Index Cond can tell very different stories.
⬇️ Filter Early
Eliminate unnecessary rows in DocDB — or through a selective access path — whenever the query semantics allow it.
💰 Distributed Work Costs
A small result set can still produce substantial scanning, RPC, and network activity.

In a distributed database, query tuning is not only about reducing execution time.

Sometimes reviewing where your filters are actually evaluated can save you 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. 😂