explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KMKr

Settings
# exclusive inclusive rows x rows loops node
1. 3.533 718.148 ↑ 1.0 1 1

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

2. 0.000 714.615 ↓ 225.6 37,450 1

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

3. 27.221 678.700 ↓ 225.6 37,450 1

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

4. 7.224 651.479 ↓ 225.6 37,450 1

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

5. 21.081 606.805 ↓ 225.6 37,450 1

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

6. 21.255 585.724 ↓ 225.6 37,450 1

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

7. 20.801 564.469 ↓ 225.6 37,450 1

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

8. 21.832 543.668 ↓ 225.6 37,450 1

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

9. 27.868 521.836 ↓ 225.6 37,450 1

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

10. 26.691 493.968 ↓ 225.6 37,450 1

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

11. 29.694 429.827 ↓ 225.6 37,450 1

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

12. 15.415 362.683 ↓ 225.6 37,450 1

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

13. 66.378 309.818 ↓ 225.6 37,450 1

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

  • -> 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. 63.737 243.440 ↓ 225.6 37,450 1

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

  • -> 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)
15. 67.094 179.703 ↓ 225.6 37,450 1

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

  • -> 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)
16. 80.135 112.609 ↓ 225.6 37,450 1

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

  • -> 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)
17. 5.540 32.474 ↓ 225.6 37,450 1

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

  • Merge Cond: (projectstreams.stream_id = stream.id)
  • -> Index Only Scan using stream_pkey on stream (cost=0.27..23.82 rows=237 width=8) (actual time=0.008..0.044 rows=194 loops
  • Index Cond: (id = defectocc.defect_instance_details_id)
18. 26.260 26.934 ↓ 225.6 37,450 1

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

  • -> Bitmap Heap Scan on defect_instance defectocc (cost=20.63..3559.12 rows=1057 width=32) (actual time=0.543..1.986 r
  • Heap Fetches: 0
19. 0.025 0.674 ↓ 11.0 11 1

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

  • -> 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
  • -> Bitmap Index Scan on testbed_defect_instance_snapshot_idx (cost=0.00..20.36 rows=1057 width=0) (actual time=
20. 0.037 0.649 ↓ 11.0 11 1

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

  • Join Filter: (streamelement.stream_id = projectstreams.stream_id)
  • Rows Removed by Join Filter: 121
  • -> 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
  • Index Cond: (id = '10273'::bigint)
  • Heap Fetches: 0
  • Index Cond: (snapshot_element_id = snapshotelement.id)
21. 0.011 0.612 ↑ 1.3 11 12

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

22. 0.121 0.601 ↑ 1.3 11 1

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

  • Hash Cond: (snapshotelement.stream_element_id = streamelement.id)
  • -> 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
23. 0.480 0.480 ↑ 1.0 711 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
  • -> Seq Scan on stream_element streamelement (cost=0.00..19.11 rows=711 width=16) (actua
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
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)
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
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
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
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
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
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
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)
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
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