explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 92tGY

Settings
# exclusive inclusive rows x rows loops node
1. 43.479 43.479 ↑ 6,557.1 1,190 1

CTE Scan on _resolution (cost=9,661,632.29..9,856,706.29 rows=7,802,960 width=20) (actual time=0.047..43.479 rows=1,190 loops=1)

  • Output: _resolution.document_id, _resolution.r_id, strpos(replace(_resolution.sub_deleted_str, '1+2'::text, '+'::text), '1'::text)
  • Buffers: shared hit=5830 read=23
  • I/O Timings: read=6.467
2.          

CTE _resolution

3. 1.450 42.297 ↑ 6,557.1 1,190 1

Recursive Union (cost=0.00..9,661,632.29 rows=7,802,960 width=48) (actual time=0.036..42.297 rows=1,190 loops=1)

  • Buffers: shared hit=5830 read=23
  • I/O Timings: read=6.467
4. 0.014 0.047 ↑ 1.0 100 1

Limit (cost=0.00..1.64 rows=100 width=18) (actual time=0.027..0.047 rows=100 loops=1)

  • Output: r.document_id, r.r_id, r.sub_deleted_str
  • Buffers: shared hit=1
5. 0.033 0.033 ↑ 1,931.3 100 1

Seq Scan on rem_pkg._temp_t5_hierarch_1 r (cost=0.00..3,162.34 rows=193,134 width=18) (actual time=0.024..0.033 rows=100 loops=1)

  • Output: r.document_id, r.r_id, r.sub_deleted_str
  • Buffers: shared hit=1
6. 1.322 40.800 ↑ 11,474.8 68 16

Nested Loop (cost=0.57..950,557.15 rows=780,286 width=48) (actual time=0.017..2.550 rows=68 loops=16)

  • Output: r_1.document_id, r_1.id, ((_r.sub_deleted_str || '+'::text) || (COALESCE((CASE WHEN (r_1.vzamen = r_1.parent_id) THEN 2 ELSE (r_1.has_vzamen)::integer END)::character varying, ''::character varying))::text)
  • Buffers: shared hit=5829 read=23
  • I/O Timings: read=6.467
7. 0.208 0.208 ↑ 13.5 74 16

WorkTable Scan on _resolution _r (cost=0.00..20.00 rows=1,000 width=40) (actual time=0.000..0.013 rows=74 loops=16)

  • Output: _r.document_id, _r.r_id, _r.sub_deleted_str
8. 39.270 39.270 ↑ 780.0 1 1,190

Index Scan using idx_resolution_parent_id on doc.resolution r_1 (cost=0.57..931.03 rows=780 width=34) (actual time=0.028..0.033 rows=1 loops=1,190)

  • Output: r_1.document_id, r_1.id, r_1.vzamen, r_1.parent_id, r_1.has_vzamen
  • Index Cond: (r_1.parent_id = _r.r_id)
  • Buffers: shared hit=5829 read=23
  • I/O Timings: read=6.467