explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HZS3 : Optimization for: jobs; plan #Nv1p

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 9.990 3,414.783 ↑ 2.3 3,108 1

Nested Loop (cost=166.22..55,875.14 rows=7,084 width=1,326) (actual time=404.719..3,414.783 rows=3,108 loops=1)

2.          

CTE started

3. 10.823 10.823 ↓ 210.1 4,622 1

Index Scan using idx_entity_op_created on ticket (cost=0.56..46.78 rows=22 width=37) (actual time=0.053..10.823 rows=4,622 loops=1)

  • Index Cond: (((op_type)::text = 'SDD_SNAPSHOT'::text) AND ((entity_id)::text = 'sdb-services-test10:sdb17'::text))
4.          

CTE completed

5. 14.935 14.935 ↓ 219.8 4,615 1

Index Scan using idx_entity_op_created on ticket ticket_1 (cost=0.56..45.01 rows=21 width=37) (actual time=0.052..14.935 rows=4,615 loops=1)

  • Index Cond: (((op_type)::text = 'SDD_COPY_EXTENT_COMPLETE'::text) AND ((entity_id)::text = 'sdb-services-test10:sdb17'::text))
  • Filter: ((status)::text = ANY ('{COMPLETED,ABORT}'::text[]))
6. 8.422 21.489 ↓ 210.1 4,622 1

HashAggregate (cost=0.49..0.71 rows=22 width=516) (actual time=16.131..21.489 rows=4,622 loops=1)

  • Group Key: (started.job_id)::text
7. 13.067 13.067 ↓ 210.1 4,622 1

CTE Scan on started (cost=0.00..0.44 rows=22 width=516) (actual time=0.056..13.067 rows=4,622 loops=1)

8. 2,700.737 3,383.304 ↑ 322.0 1 4,622

Bitmap Heap Scan on ticket t (cost=73.94..2,532.35 rows=322 width=1,326) (actual time=0.732..0.732 rows=1 loops=4,622)

  • Recheck Cond: ((job_id)::text = (started.job_id)::text)
  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 461
  • Heap Blocks: exact=311534
9. 665.568 665.568 ↑ 1.4 465 4,622

Bitmap Index Scan on idx_job_op (cost=0.00..73.39 rows=644 width=0) (actual time=0.144..0.144 rows=465 loops=4,622)

  • Index Cond: ((job_id)::text = (started.job_id)::text)
10.          

SubPlan (for Bitmap Heap Scan)

11. 16.999 16.999 ↓ 219.8 4,615 1

CTE Scan on completed (cost=0.00..0.42 rows=21 width=516) (actual time=0.053..16.999 rows=4,615 loops=1)

Planning time : 0.434 ms
Execution time : 3,415.167 ms