explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wL7

Settings
# exclusive inclusive rows x rows loops node
1. 17.603 41,739.188 ↑ 1.0 1 1

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

  • Buffers: shared hit=1418320 read=18886
2. 21.580 41,721.585 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1418320 read=18886
3. 34.268 41,625.105 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1343421 read=18884
4. 44.078 41,553.387 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1305971 read=18883
5. 16.917 41,396.959 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1193629 read=18875
6. 16.708 41,342.592 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1156178 read=18875
7. 18.197 41,288.434 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1118727 read=18875
8. 20.902 41,232.787 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1081276 read=18875
9. 45.007 41,174.435 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1043825 read=18875
10. 49.368 41,091.978 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=1006375 read=18874
11. 39.621 37,447.410 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=857345 read=17989
12. 41.132 36,022.139 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=708243 read=17199
13. 2,717.364 35,456.707 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=595932 read=17056
  • -> Index Scan using stream_defect_pkey on stream_defect streamdefect (cost=0.42..0.44 rows=1 width=32) (actual time=0.071..0.071 rows=1 loops=37450
14. 2,505.136 32,739.343 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=446823 read=16261
  • -> Index Scan using stream_file_pkey on stream_file streamfile (cost=0.42..0.44 rows=1 width=16) (actual time=0.066..0.066 rows=1 loops=37450
  • Index Cond: (id = defectocc.stream_defect_id)
  • Buffers: shared hit=149109 read=795
15. 13,961.065 30,234.207 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=297411 read=15773
  • -> Index Scan using file_instance_pkey on file_instance fileinst (cost=0.44..0.60 rows=1 width=16) (actual time=0.372..0.372 rows=1 loo
  • Index Cond: (id = fileinst.stream_file_id)
  • Buffers: shared hit=149412 read=488
16. 2,270.298 16,273.142 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=149577 read=13672
  • -> 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=147834 read=2101
17. 43.856 14,002.844 ↓ 225.6 37,450 1

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

  • Merge Cond: (projectstreams.stream_id = stream.id)
  • Buffers: shared hit=2879 read=10466
  • -> Index Only Scan using stream_pkey on stream (cost=0.27..23.82 rows=237 width=8) (actual time=15.058..15.127 rows=194 loo
  • Index Cond: (id = defectocc.defect_instance_details_id)
  • Buffers: shared hit=146698 read=3206
18. 13,871.313 13,958.988 ↓ 225.6 37,450 1

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

  • Buffers: shared hit=2877 read=10464
  • -> Bitmap Heap Scan on defect_instance defectocc (cost=20.63..3559.12 rows=1057 width=32) (actual time=15.791..1258.9
  • Heap Fetches: 0
  • Buffers: shared hit=2 read=2
19. 10.015 87.675 ↓ 11.0 11 1

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

  • Buffers: shared hit=37 read=44
  • -> Index Only Scan using project_pkey on project (cost=0.15..4.17 rows=1 width=8) (actual time=0.907..0.909 row
  • Recheck Cond: (snapshot_element_id = snapshotelement.id)
  • Heap Blocks: exact=13125
  • Buffers: shared hit=2840 read=10420
  • -> Bitmap Index Scan on testbed_defect_instance_snapshot_idx (cost=0.00..20.36 rows=1057 width=0) (actual time=
20. 10.460 77.660 ↓ 11.0 11 1

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

  • Join Filter: (streamelement.stream_id = projectstreams.stream_id)
  • Rows Removed by Join Filter: 121
  • Buffers: shared hit=27 read=42
  • -> 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 read=4
  • Index Cond: (id = '10273'::bigint)
  • Heap Fetches: 0
  • Buffers: shared hit=10 read=2
  • Index Cond: (snapshot_element_id = snapshotelement.id)
  • Buffers: shared hit=19 read=116
21. 0.082 67.200 ↑ 1.3 11 12

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

  • Buffers: shared hit=27 read=38
22. 66.522 67.118 ↑ 1.3 11 1

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

  • Hash Cond: (snapshotelement.stream_element_id = streamelement.id)
  • Buffers: shared hit=27 read=38
  • -> 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=21 read=29
23. 0.596 0.596 ↑ 1.0 711 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
  • Buffers: shared hit=3 read=9
  • -> Seq Scan on stream_element streamelement (cost=0.00..19.11 rows=711 width=16) (actua
  • Buffers: shared hit=3 read=9
24. 524.300 524.300 ↑ 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.014..0.014 rows=1 loops=37,450)

  • Index Cond: (id = streamdefect.merged_defect_id)
  • Heap Fetches: 0
  • Buffers: shared hit=112311 read=143
25. 1,385.650 1,385.650 ↑ 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.037..0.037 rows=1 loops=37,450)

  • Index Cond: (id = streamdefect.defect_triage_id)
  • Buffers: shared hit=149102 read=790
26. 3,595.200 3,595.200 ↑ 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.096..0.096 rows=1 loops=37,450)

  • Index Cond: (id = streamfile.file_path_id)
  • Heap Fetches: 37450
  • Buffers: shared hit=149030 read=885
27. 37.450 37.450 ↑ 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.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_classification_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37450 read=1
28. 37.450 37.450 ↑ 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.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_action_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
29. 37.450 37.450 ↑ 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.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_fix_target_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
30. 37.450 37.450 ↑ 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.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_severity_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
31. 37.450 37.450 ↑ 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.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = defecttriage.current_legacy_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37451
32. 112.350 112.350 ↑ 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.003..0.003 rows=1 loops=37,450)

  • Index Cond: (id = streamdefect.checker_properties_id)
  • Buffers: shared hit=112342 read=8
33. 37.450 37.450 ↑ 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.001..0.001 rows=1 loops=37,450)

  • Index Cond: (id = chk_prop.checker_category_id)
  • Heap Fetches: 0
  • Buffers: shared hit=37450 read=1
34. 74.900 74.900 ↑ 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.002..0.002 rows=1 loops=37,450)

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