Rows Removed by Filter in PostgreSQL EXPLAIN

What it does

When a plan step has a Filter: line, PostgreSQL checks that condition against every row the step produces and drops the ones that fail. EXPLAIN ANALYZE counts the dropped rows and prints them underneath as Rows Removed by Filter.

The filter is the part of your WHERE clause that the step couldn’t use to find rows. A Seq Scan has no way to find rows, so its whole condition is a filter. An Index Scan uses some conditions to search the index (Index Cond:) and applies the rest as a filter to the rows the index returned.

Two related lines work the same way:

• Rows Removed by Join Filter: rows a join produced and then rejected because a join condition that isn’t the join key failed.
• Rows Removed by Index Recheck: rows rechecked on a bitmap heap scan’s lossy pages; see lossy heap blocks.

When you see it

Only with EXPLAIN ANALYZE, and only on steps that have a Filter: (or Join Filter:) line and removed at least one row. Plain EXPLAIN shows the Filter: line but not the count, because nothing has run.

Reading its numbers

Put it next to the step’s actual rows:

Seq Scan on orders  (cost=0.00..23094.00 rows=9867 width=34) (actual time=3.184..41.745 rows=10000.00 loops=1)
  Filter: (status = 'cancelled'::text)
  Rows Removed by Filter: 990000

The scan read 1,000,000 rows (10,000 kept + 990,000 removed) to return 1%. Every removed row still cost a page read and a comparison.

• It’s an average per loop. Like rows, it’s divided by loops. In a parallel plan with loops=3, Rows Removed by Filter: 330000 means about 990,000 in total. On the inner side of a nested loop, multiply by the loop count. See loops and actual time.
• 0 isn’t printed. If the line is missing but Filter: is there, every row passed.
• Compare with Buffers. A high removed count with a high Buffers count on the same step is where the time goes.

When it’s a problem

When the removed count is large compared with the rows kept, on a step that takes a real share of the query’s time. Removing 19,000 rows from a 20,000-row table in 1 ms isn’t worth an index; removing 990,000 rows from a table you query 50 times a second is.

Fixes, most common first:

1. Index the filtered column. If the filter is selective (it keeps a small share of rows), an index lets the planner find matching rows instead of reading all of them. Build it on a busy table with CREATE INDEX CONCURRENTLY.
2. Extend the index the step already uses. If an Index Scan shows Index Cond: on one column and Filter: on another, an index with both columns moves the filter into the index condition. Put columns compared with = first and the range column last.
3. A partial index. For a filter on a fixed value, such as status = 'cancelled', an index with the same WHERE clause (CREATE INDEX ON orders (created_at) WHERE status = 'cancelled') holds only those rows.
4. Make the condition indexable. WHERE lower(email) = $1 can’t use an index on email; an index on lower(email) can. Casting the column (created_at::date = …) has the same effect: write the condition on the bare column (created_at >= … AND created_at < …).

If the filter keeps most of the table, the removed count is small anyway, and a sequential scan is the right plan.

Example

PostgreSQL 18.6, default settings, in a scratch schema:

CREATE SCHEMA seo_terms;
SET search_path = seo_terms;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL,
  total       numeric(10,2) NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 20000,
       CASE WHEN i % 100 = 0 THEN 'cancelled' WHEN i % 10 = 0 THEN 'pending' ELSE 'shipped' END,
       timestamptz '2025-01-01' + i * interval '30 seconds',
       ((i::bigint * 37) % 50000) / 100.0
FROM generate_series(1, 1000000) AS i;
ANALYZE orders;
SET max_parallel_workers_per_gather = 0;  -- one process, so the counts are totals

No index on status, so the scan reads the whole table:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'cancelled';

Seq Scan on orders  (cost=0.00..23094.00 rows=9867 width=34) (actual time=3.184..41.745 rows=10000.00 loops=1)
  Filter: (status = 'cancelled'::text)
  Rows Removed by Filter: 990000
  Buffers: shared hit=10473 read=121
Planning Time: 0.049 ms
Execution Time: 42.055 ms

With the default two parallel workers, the same query splits the table three ways and the count is per process:

Gather  (cost=1000.00..17789.03 rows=9867 width=34) (actual time=3.689..24.958 rows=10000.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=10367 read=227
  ->  Parallel Seq Scan on orders  (cost=0.00..15802.33 rows=4111 width=34) (actual time=1.874..20.326 rows=3333.33 loops=3)
        Filter: (status = 'cancelled'::text)
        Rows Removed by Filter: 330000
        Buffers: shared hit=10367 read=227

Now an index on created_at and a query for one day’s cancelled orders. The index finds the day; the status is checked afterwards:

CREATE INDEX orders_created_at_idx ON orders (created_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02' AND status = 'cancelled';

Index Scan using orders_created_at_idx on orders  (cost=0.42..137.64 rows=29 width=14) (actual time=0.042..0.294 rows=28.00 loops=1)
  Index Cond: ((created_at >= '2025-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-03-02 00:00:00+00'::timestamp with time zone))
  Filter: (status = 'cancelled'::text)
  Rows Removed by Filter: 2852
  Index Searches: 1
  Buffers: shared hit=36
Planning Time: 0.254 ms
Execution Time: 0.324 ms

28 rows kept, 2,852 fetched and thrown away. An index on both columns, equality column first, moves the status check into the index:

CREATE INDEX orders_status_created_at_idx ON orders (status, created_at);

Index Scan using orders_status_created_at_idx on orders  (cost=0.42..79.52 rows=29 width=14) (actual time=0.026..0.047 rows=28.00 loops=1)
  Index Cond: ((status = 'cancelled'::text) AND (created_at >= '2025-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-03-02 00:00:00+00'::timestamp with time zone))
  Index Searches: 1
  Buffers: shared hit=24 read=3
Planning Time: 0.147 ms
Execution Time: 0.060 ms

No Filter: line and no removed rows: the index returned only the 28 rows wanted, and execution went from 0.324 ms to 0.060 ms. PostgreSQL 14–17 print the same lines without Index Searches, and show actual rows as whole numbers.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step, which is usually the one with the biggest removed count. You can also paste a plan into the free plan visualizer.