explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 583.237 4,269.457 ↑ 4.6 3,108 1

Hash Anti Join (cost=166.51..56,081.28 rows=14,144 width=1,326) (actual time=617.919..4,269.457 rows=3,108 loops=1)

  • Hash Cond: ((t.job_id)::text = (completed.job_id)::text)
2.          

CTE started

3. 13.222 13.222 ↓ 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..13.222 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. 13.262 13.262 ↓ 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.055..13.262 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. 1,270.602 3,669.190 ↓ 150.7 2,134,636 1

Nested Loop (cost=74.04..55,810.16 rows=14,168 width=1,326) (actual time=18.797..3,669.190 rows=2,134,636 loops=1)

7. 7.368 22.880 ↓ 210.1 4,622 1

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

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

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

9. 1,696.274 2,375.708 ↑ 1.4 462 4,622

Bitmap Heap Scan on ticket t (cost=73.55..2,530.35 rows=644 width=1,326) (actual time=0.161..0.514 rows=462 loops=4,622)

  • Recheck Cond: ((job_id)::text = (started.job_id)::text)
  • Heap Blocks: exact=311535
10. 679.434 679.434 ↑ 1.4 465 4,622

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

  • Index Cond: ((job_id)::text = (started.job_id)::text)
11. 1.341 17.030 ↓ 219.8 4,615 1

Hash (cost=0.42..0.42 rows=21 width=516) (actual time=17.030..17.030 rows=4,615 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 375kB
12. 15.689 15.689 ↓ 219.8 4,615 1

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

Planning time : 0.435 ms
Execution time : 4,270.078 ms