explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AE2J

Settings
# exclusive inclusive rows x rows loops node
1. 4.017 742.722 ↑ 1.0 1 1

Aggregate (cost=4,657.97..4,657.98 rows=1 width=8) (actual time=742.722..742.722 rows=1 loops=1)

  • Buffers: shared hit=1437200
2. 0.003 738.705 ↓ 225.6 37,450 1

Nested Loop (cost=53.97..4,657.56 rows=166 width=0) (actual time=4.904..738.705 rows=37,450 loops=1)

  • Buffers: shared hit=1437200
3. 28.485 701.252 ↓ 225.6 37,450 1

Nested Loop (cost=53.69..4,606.51 rows=166 width=8) (actual time=4.900..701.252 rows=37,450 loops=1)

  • Buffers: shared hit=1362299
4. 8.910 672.767 ↓ 225.6 37,450 1

Nested Loop (cost=53.55..4,579.18 rows=166 width=16) (actual time=4.897..672.767 rows=37,450 loops=1)

  • Buffers: shared hit=1324848
5. 22.670 626.407 ↓ 225.6 37,450 1

Nested Loop (cost=53.27..4,530.16 rows=166 width=8) (actual time=4.893..626.407 rows=37,450 loops=1)

  • Buffers: shared hit=1212498
6. 22.043 603.737 ↓ 225.6 37,450 1

Nested Loop (cost=53.13..4,504.08 rows=166 width=16) (actual time=4.891..603.737 rows=37,450 loops=1)

  • Buffers: shared hit=1175047
7. 21.851 581.694 ↓ 225.6 37,450 1

Nested Loop (cost=52.99..4,477.99 rows=166 width=24) (actual time=4.889..581.694 rows=37,450 loops=1)

  • Buffers: shared hit=1137596
8. 22.931 559.843 ↓ 225.6 37,450 1

Nested Loop (cost=52.85..4,451.91 rows=166 width=32) (actual time=4.887..559.843 rows=37,450 loops=1)

  • Buffers: shared hit=1100145
9. 29.440 536.912 ↓ 225.6 37,450 1

Nested Loop (cost=52.71..4,425.82 rows=166 width=40) (actual time=4.885..536.912 rows=37,450 loops=1)

  • Buffers: shared hit=1062694
10. 28.447 507.472 ↓ 225.6 37,450 1

Nested Loop (cost=52.57..4,399.74 rows=166 width=48) (actual time=4.882..507.472 rows=37,450 loops=1)

  • Buffers: shared hit=1025243
11. 31.750 441.575 ↓ 225.6 37,450 1

Nested Loop (cost=52.15..4,318.53 rows=166 width=56) (actual time=4.876..441.575 rows=37,450 loops=1)

  • Buffers: shared hit=875328
12. 16.887 372.375 ↓ 225.6 37,450 1

Nested Loop (cost=51.73..4,232.86 rows=166 width=24) (actual time=4.870..372.375 rows=37,450 loops=1)

  • Buffers: shared hit=725436
13. 68.756 318.038 ↓ 225.6 37,450 1

Nested Loop (cost=51.31..4,159.26 rows=166 width=32) (actual time=4.864..318.038 rows=37,450 loops=1)

  • Buffers: shared hit=612982
  • -> Index Scan using stream_defect_pkey on stream_defect streamdefect (cost=0.42..0.44 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=37450
14. 65.161 249.282 ↓ 225.6 37,450 1

Nested Loop (cost=50.89..4,086.28 rows=166 width=16) (actual time=4.858..249.282 rows=37,450 loops=1)

  • Buffers: shared hit=463078
  • -> Index Scan using stream_file_pkey on stream_file streamfile (cost=0.42..0.44 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=37450
  • Index Cond: (id = defectocc.stream_defect_id)
  • Buffers: shared hit=149904
15. 69.106 184.121 ↓ 225.6 37,450 1

Nested Loop (cost=50.46..4,012.45 rows=166 width=16) (actual time=4.852..184.121 rows=37,450 loops=1)

  • Buffers: shared hit=313178
  • -> Index Scan using file_instance_pkey on file_instance fileinst (cost=0.44..0.60 rows=1 width=16) (actual time=0.001..0.001 rows=1 loo
  • Index Cond: (id = fileinst.stream_file_id)
  • Buffers: shared hit=149900
16. 80.881 115.015 ↓ 225.6 37,450 1

Nested Loop (cost=50.03..3,912.89 rows=166 width=16) (actual time=4.844..115.015 rows=37,450 loops=1)

  • Buffers: shared hit=163243
  • -> Index Scan using defect_instance_details_pkey on defect_instance_details did (cost=0.42..0.44 rows=1 width=16) (actual time=0.
  • Index Cond: (id = defectocc.file_instance_id)
  • Buffers: shared hit=149935
17. 6.196 34.134 ↓ 225.6 37,450 1

Merge Join (cost=49.60..3,839.62 rows=166 width=24) (actual time=4.836..34.134 rows=37,450 loops=1)

  • Merge Cond: (projectstreams.stream_id = stream.id)
  • Buffers: shared hit=13339
  • -> Index Only Scan using stream_pkey on stream (cost=0.27..23.82 rows=237 width=8) (actual time=0.005..0.022 rows=194 loops
  • Index Cond: (id = defectocc.defect_instance_details_id)
  • Buffers: shared hit=149904
18. 27.243 27.938 ↓ 225.6 37,450 1

Nested Loop (cost=49.33..3,813.12 rows=166 width=40) (actual time=4.806..27.938 rows=37,450 loops=1)

  • Buffers: shared hit=13335
  • -> Bitmap Heap Scan on defect_instance defectocc (cost=20.63..3559.12 rows=1057 width=32) (actual time=0.533..1.995 r
  • Heap Fetches: 0
  • Buffers: shared hit=4
19. 0.026 0.695 ↓ 11.0 11 1

Nested Loop (cost=28.71..243.43 rows=1 width=24) (actual time=0.640..0.695 rows=11 loops=1)

  • Buffers: shared hit=75
  • -> Index Only Scan using project_pkey on project (cost=0.15..4.17 rows=1 width=8) (actual time=0.001..0.002 row
  • Recheck Cond: (snapshot_element_id = snapshotelement.id)
  • Heap Blocks: exact=13125
  • Buffers: shared hit=13260
  • -> Bitmap Index Scan on testbed_defect_instance_snapshot_idx (cost=0.00..20.36 rows=1057 width=0) (actual time=
20. 0.033 0.669 ↓ 11.0 11 1

Nested Loop (cost=28.56..239.25 rows=1 width=32) (actual time=0.633..0.669 rows=11 loops=1)

  • Join Filter: (streamelement.stream_id = projectstreams.stream_id)
  • Rows Removed by Join Filter: 121
  • Buffers: shared hit=63
  • -> Index Only Scan using uk_cufwe1r645y6j3x2liohv1ck0 on project_stream projectstreams (cost=0.27..4.48 r
  • Index Cond: (project_id = '10273'::bigint)
  • Heap Fetches: 0
  • Buffers: shared hit=4
  • Index Cond: (id = '10273'::bigint)
  • Heap Fetches: 0
  • Buffers: shared hit=12
  • Index Cond: (snapshot_element_id = snapshotelement.id)
  • Buffers: shared hit=135
21. 0.017 0.636 ↑ 1.3 11 12

Materialize (cost=28.29..232.29 rows=14 width=16) (actual time=0.045..0.053 rows=11 loops=12)

  • Buffers: shared hit=59
22. 0.120 0.619 ↑ 1.3 11 1

Hash Join (cost=28.29..232.22 rows=14 width=16) (actual time=0.533..0.619 rows=11 loops=1)

  • Hash Cond: (snapshotelement.stream_element_id = streamelement.id)
  • Buffers: shared hit=59
  • -> Index Scan using testbed_snapshot_element_snapshot_idx on snapshot_element snapshotelement
  • Index Cond: (snapshot_id = ANY ('{21656,21543,21494,21423,21283,21437,21505,21585,21437,2
  • Filter: ((dtype)::text = ANY ('{StcSE,DynSE}'::text[]))
  • Rows Removed by Filter: 22
  • Buffers: shared hit=47
23. 0.499 0.499 ↑ 1.0 711 1

Hash (cost=19.11..19.11 rows=711 width=16) (actual time=0.499..0.499 rows=711 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
  • Buffers: shared hit=12
  • -> Seq Scan on stream_element streamelement (cost=0.00..19.11 rows=711 width=16) (actua
  • Buffers: shared hit=12
24. 37.450 37.450 ↑ 1.0 1 37,450

Index Only Scan using merged_defect_pkey on merged_defect mergeddefect (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = streamdefect.merged_defect_id)
  • Heap Fetches: 0
  • Buffers: shared hit=112454
25. 37.450 37.450 ↑ 1.0 1 37,450

Index Scan using defect_triage_pkey on defect_triage defecttriage (cost=0.42..0.52 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = streamdefect.defect_triage_id)
  • Buffers: shared hit=149892
26. 37.450 37.450 ↑ 1.0 1 37,450

Index Only Scan using file_path_pkey on file_path filepath (cost=0.42..0.49 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = streamfile.file_path_id)
  • Heap Fetches: 37450
  • Buffers: shared hit=149915
27. 0.000 0.000 ↑ 1.0 1 37,450

Index Only Scan using dynamic_enum_pkey on dynamic_enum classification (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_classification_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
28. 0.000 0.000 ↑ 1.0 1 37,450

Index Only Scan using dynamic_enum_pkey on dynamic_enum action (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_action_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
29. 0.000 0.000 ↑ 1.0 1 37,450

Index Only Scan using dynamic_enum_pkey on dynamic_enum fixtarget (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_fix_target_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
30. 0.000 0.000 ↑ 1.0 1 37,450

Index Only Scan using dynamic_enum_pkey on dynamic_enum severity (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_severity_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
31. 0.000 0.000 ↑ 1.0 1 37,450

Index Only Scan using dynamic_enum_pkey on dynamic_enum legacy (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_legacy_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
32. 37.450 37.450 ↑ 1.0 1 37,450

Index Scan using checker_properties_pkey on checker_properties chk_prop (cost=0.28..0.30 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = streamdefect.checker_properties_id)
  • Buffers: shared hit=112350
33. 0.000 0.000 ↑ 1.0 1 37,450

Index Only Scan using checker_category_pkey on checker_category chk_cat (cost=0.14..0.17 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=37,450)

  • Index Cond: (id = chk_prop.checker_category_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
34. 37.450 37.450 ↑ 1.0 1 37,450

Index Only Scan using checker_type_pkey on checker_type chk_type (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = chk_prop.checker_type_id)
  • Heap Fetches: 0
  • Buffers: shared hit=74901