HTML
SOURCE
QUERY
REFORMATTED QUERY
STATS

#	exclusive	inclusive	rows x	rows	loops	node
1.	0.788	2.023	↓ 0.0	0	1	→ Delete on public.kv (cost=932.69..10,485.71 rows=0 width=0) (actual time=2.022..2.023 rows=0 loops=1) Query Identifier: -8226339520301693636 Buffers: shared hit=2,348
2.						InitPlan 1 (for Delete) (returns $0)
3.	0.061	0.741	↑ 1.0	1	1	→ Limit (cost=36.67..36.71 rows=1 width=8) (actual time=0.740..0.741 rows=1 loops=1) Output: kv_1.expires Buffers: shared hit=1,006
4.	0.680	0.680	↑ 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.029..0.680 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.	0.154	1.235	↑ 33.3	1,000	1	→ Bitmap Heap Scan on public.kv (cost=895.98..10,449.00 rows=33,335 width=6) (actual time=0.898..1.235 rows=1,000 loops=1) Output: kv.ctid Recheck Cond: ((kv.expires IS NOT NULL) AND (kv.expires <= COALESCE($0, now()))) Heap Blocks: exact=337 Buffers: shared hit=1,348
6.	0.340	0.850	↑ 33.3	1,000	1	→ Bitmap Index Scan on kv_expires_idx (cost=0.00..887.65 rows=33,335 width=0) (actual time=0.850..0.850 rows=1,000 loops=1) Index Cond: ((kv.expires IS NOT NULL) AND (kv.expires <= COALESCE($0, now()))) Buffers: shared hit=1,011

Planning time	:	0.165 ms
Execution time	:	2.063 ms

                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on public.kv  (cost=932.69..10485.71 rows=0 width=0) (actual time=2.022..2.023 rows=0 loops=1)
   Buffers: shared hit=2348
   InitPlan 1 (returns $0)
     ->  Limit  (cost=36.67..36.71 rows=1 width=8) (actual time=0.740..0.741 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.029..0.680 rows=1000 loops=1)
                 Output: kv_1.expires
                 Index Cond: (kv_1.expires <= now())
                 Heap Fetches: 1000
                 Buffers: shared hit=1006
   ->  Bitmap Heap Scan on public.kv  (cost=895.98..10449.00 rows=33335 width=6) (actual time=0.898..1.235 rows=1000 loops=1)
         Output: kv.ctid
         Recheck Cond: ((kv.expires IS NOT NULL) AND (kv.expires <= COALESCE($0, now())))
         Heap Blocks: exact=337
         Buffers: shared hit=1348
         ->  Bitmap Index Scan on kv_expires_idx  (cost=0.00..887.65 rows=33335 width=0) (actual time=0.850..0.850 rows=1000 loops=1)
               Index Cond: ((kv.expires IS NOT NULL) AND (kv.expires <= COALESCE($0, now())))
               Buffers: shared hit=1011
 Query Identifier: -8226339520301693636
 Planning Time: 0.165 ms
 Execution Time: 2.063 ms
(22 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 off; 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 OFF;

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
Bitmap Heap Scan	1	0.154 ms	7.6 %
Bitmap Index Scan	1	0.340 ms	16.8 %
Delete	1	0.788 ms	39.0 %
Index Only Scan	1	0.680 ms	33.6 %
Limit	1	0.061 ms	3.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
public.kv	3	1.622 ms	80.2 %
Bitmap Heap Scan	1	0.154 ms	9.5 %
Delete	1	0.788 ms	48.6 %
Index Only Scan	1	0.680 ms	41.9 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tFfB

Color mode:

Visible columns:

I/O stats

Per node type stats

Per table stats