explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O2zy

Settings
# exclusive inclusive rows x rows loops node
1. 619.354 619.354 ↑ 34.5 193,140 1

CTE Scan on _resolution (cost=2,939,259.56..3,072,675.84 rows=6,670,814 width=16) (actual time=0.057..619.354 rows=193,140 loops=1)

  • Output: _resolution.document_id, _resolution.r_id
  • Buffers: shared hit=7955
2.          

CTE _resolution

3. 123.336 569.461 ↑ 34.5 193,140 1

Recursive Union (cost=0.00..2,939,259.56 rows=6,670,814 width=48) (actual time=0.052..569.461 rows=193,140 loops=1)

  • Buffers: shared hit=7955
4. 18.647 18.647 ↑ 1.0 193,134 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.039..18.647 rows=193,134 loops=1)

  • Output: r.document_id, r.r_id, r.sub_deleted_str
  • Buffers: shared hit=1231
5. 48.330 427.478 ↑ 215,922.7 3 2

Merge Join (cost=253,917.19..280,268.09 rows=647,768 width=48) (actual time=205.700..213.739 rows=3 loops=2)

  • 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)
  • Merge Cond: (r_1.parent_id = _r.r_id)
  • Buffers: shared hit=6724
6. 83.618 307.076 ↑ 1.0 99,214 2

Sort (cost=13,647.19..13,897.19 rows=100,000 width=34) (actual time=141.685..153.538 rows=99,214 loops=2)

  • Output: r_1.document_id, r_1.id, r_1.vzamen, r_1.parent_id, r_1.has_vzamen
  • Sort Key: r_1.parent_id
  • Sort Method: quicksort Memory: 10885kB
  • Buffers: shared hit=6724
7. 21.050 223.458 ↑ 1.0 100,000 2

Subquery Scan on r_1 (cost=0.00..5,342.37 rows=100,000 width=34) (actual time=0.023..111.729 rows=100,000 loops=2)

  • Output: r_1.document_id, r_1.id, r_1.vzamen, r_1.parent_id, r_1.has_vzamen
  • Buffers: shared hit=6724
8. 13.236 202.408 ↑ 1.0 100,000 2

Limit (cost=0.00..4,342.37 rows=100,000 width=1,684) (actual time=0.022..101.204 rows=100,000 loops=2)

  • Output: r_2.id, r_2.document_id, r_2.parent_id, NULL::bigint, NULL::character varying(100), NULL::timestamp(0) without time zone, NULL::bigint, NULL::timestamp(0) without time zone, NULL::bigint, NULL::character varying(255), NULL::timestamp(0) without time zone, NULL::bigint, NULL::smallint, NULL::bigint, NULL::character varying(255), NULL::bigint, NULL::character varying(255), NULL::character varying(255), NULL::text, NULL::text, NULL::bigint, NULL::timestamp(0) without time zone, NULL::smallint, NULL::bigint, NULL::character varying(56), NULL::timestamp(0) without time zone, NULL::bigint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::character varying(1020), NULL::smallint, NULL::smallint, NULL::character varying(80), NULL::smallint, NULL::bigint, r_2.vzamen, NULL::timestamp(0) without time zone, NULL::smallint, NULL::bigint, NULL::smallint, NULL::bigint, NULL::bigint, NULL::bigint, NULL::timestamp(0) without time zone, NULL::smallint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::bigint, NULL::bigint, NULL::smallint, NULL::smallint, NULL::bigint, NULL::character varying(144), r_2.has_vzamen
  • Buffers: shared hit=6724
9. 189.172 189.172 ↑ 2,326.4 100,000 2

Seq Scan on doc.resolution r_2 (cost=0.00..10,102,296.24 rows=232,644,624 width=1,684) (actual time=0.019..94.586 rows=100,000 loops=2)

  • Output: r_2.id, r_2.document_id, r_2.parent_id, NULL::bigint, NULL::character varying(100), NULL::timestamp(0) without time zone, NULL::bigint, NULL::timestamp(0) without time zone, NULL::bigint, NULL::character varying(255), NULL::timestamp(0) without time zone, NULL::bigint, NULL::smallint, NULL::bigint, NULL::character varying(255), NULL::bigint, NULL::character varying(255), NULL::character varying(255), NULL::text, NULL::text, NULL::bigint, NULL::timestamp(0) without time zone, NULL::smallint, NULL::bigint, NULL::character varying(56), NULL::timestamp(0) without time zone, NULL::bigint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::character varying(1020), NULL::smallint, NULL::smallint, NULL::character varying(80), NULL::smallint, NULL::bigint, r_2.vzamen, NULL::timestamp(0) without time zone, NULL::smallint, NULL::bigint, NULL::smallint, NULL::bigint, NULL::bigint, NULL::bigint, NULL::timestamp(0) without time zone, NULL::smallint, NULL::smallint, NULL::smallint, NULL::smallint, NULL::bigint, NULL::bigint, NULL::smallint, NULL::smallint, NULL::bigint, NULL::character varying(144), r_2.has_vzamen
  • Buffers: shared hit=6724
10. 53.460 72.072 ↑ 20.0 96,570 2

Sort (cost=240,270.00..245,098.35 rows=1,931,340 width=40) (actual time=26.760..36.036 rows=96,570 loops=2)

  • Output: _r.sub_deleted_str, _r.r_id
  • Sort Key: _r.r_id
  • Sort Method: quicksort Memory: 25kB
11. 18.612 18.612 ↑ 20.0 96,570 2

WorkTable Scan on _resolution _r (cost=0.00..38,626.80 rows=1,931,340 width=40) (actual time=0.002..9.306 rows=96,570 loops=2)

  • Output: _r.sub_deleted_str, _r.r_id