HTML
SOURCE
QUERY
REFORMATTED QUERY
STATS

#	exclusive	inclusive	rows x	rows	loops	node
1.	0.635	27.173	↓ 0.0	0	1	→ Delete on public.kv (cost=36.71..10,590.07 rows=0 width=0) (actual time=27.170..27.173 rows=0 loops=1) Query Identifier: -8226339520301693636 Buffers: shared hit=11,059
2.						InitPlan 1 (for Delete) (returns $0)
3.	0.060	0.758	↑ 1.0	1	1	→ Limit (cost=36.67..36.71 rows=1 width=8) (actual time=0.757..0.758 rows=1 loops=1) Output: kv_1.expires Buffers: shared hit=1,006
4.	0.698	0.698	↑ 100.0	1,000	1	→ Index Only Scan using kv_expires_idx on public.kv kv_1 (cost=0.29..3,640.82 rows=99,974 width=8) (actual time=0.025..0.698 rows=1,000 loops=1) Output: kv_1.expires Index Cond: (kv_1.expires <= now()) Heap Fetches: 1,000 Buffers: shared hit=1,006
5.	25.780	26.538	↑ 33.3	1,000 - 99,024	1	→ Seq Scan on public.kv (cost=0.00..10,553.36 rows=33,335 width=6) (actual time=11.072..26.538 rows=1,000 loops=1) Output: kv.ctid Filter: ((kv.expires IS NOT NULL) AND (kv.expires <= COALESCE($0, now()))) Rows Removed by Filter: 99,024 Buffers: shared hit=10,059

Planning time	:	0.159 ms
Execution time	:	27.216 ms

                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on public.kv  (cost=36.71..10590.07 rows=0 width=0) (actual time=27.170..27.173 rows=0 loops=1)
   Buffers: shared hit=11059
   InitPlan 1 (returns $0)
     ->  Limit  (cost=36.67..36.71 rows=1 width=8) (actual time=0.757..0.758 rows=1 loops=1)
           Output: kv_1.expires
           Buffers: shared hit=1006
           ->  Index Only Scan using kv_expires_idx on public.kv kv_1  (cost=0.29..3640.82 rows=99974 width=8) (actual time=0.025..0.698 rows=1000 loops=1)
                 Output: kv_1.expires
                 Index Cond: (kv_1.expires <= now())
                 Heap Fetches: 1000
                 Buffers: shared hit=1006
   ->  Seq Scan on public.kv  (cost=0.00..10553.36 rows=33335 width=6) (actual time=11.072..26.538 rows=1000 loops=1)
         Output: kv.ctid
         Filter: ((kv.expires IS NOT NULL) AND (kv.expires <= COALESCE($0, now())))
         Rows Removed by Filter: 99024
         Buffers: shared hit=10059
 Query Identifier: -8226339520301693636
 Planning Time: 0.159 ms
 Execution Time: 27.216 ms
(19 rows)

prepare z(integer) as DELETE FROM kv WHERE expires IS NOT NULL AND expires <= COALESCE((SELECT expires FROM kv WHERE expires IS NOT NULL AND expires <= now() ORDER BY expires LIMIT 1 OFFS
ET ($1 - 1)), now());
set enable_seqscan to on; set plan_cache_mode to force_custom_plan;
begin; explain (analyze, buffers, verbose) execute z(1000); rollback;

PREPARE z (integer) AS
DELETE FROM kv
WHERE expires IS NOT NULL
    AND expires <= COALESCE((
        SELECT
            expires FROM kv
        WHERE
            expires IS NOT NULL
            AND expires <= now()
ORDER BY expires LIMIT 1 OFFS ET ($1 - 1)), now());

SET enable_seqscan TO ON;

SET plan_cache_mode TO force_custom_plan;

BEGIN;
EXPLAIN (
    ANALYZE,
    buffers,
    VERBOSE
) EXECUTE z (1000);
ROLLBACK;

-- Formatted by pgFormatter::Beautify

I/O stats

Per node type stats

node type	count	sum of times	% of query
Delete	1	0.635 ms	2.3 %
Index Only Scan	1	0.698 ms	2.6 %
Limit	1	0.060 ms	0.2 %
Seq Scan	1	25.780 ms	94.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
public.kv	3	27.113 ms	99.8 %
Delete	1	0.635 ms	2.3 %
Index Only Scan	1	0.698 ms	2.6 %
Seq Scan	1	25.780 ms	95.1 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ur2W

Color mode:

Visible columns:

I/O stats

Per node type stats

Per table stats