explain.depesz.com

PostgreSQL's explain analyze made readable

Result: isnL

Settings
# exclusive inclusive rows x rows loops node
1. 457.677 7,569.818 ↓ 103,524.0 103,524 1

Sort (cost=15,019.35..15,019.35 rows=1 width=881) (actual time=7,485.248..7,569.818 rows=103,524 loops=1)

  • Sort Key: w.created_at DESC, w.number DESC, sp.id DESC
  • Sort Method: external merge Disk: 43,976kB
2. 116.680 7,112.141 ↓ 103,524.0 103,524 1

Nested Loop Left Join (cost=5,496.71..15,019.34 rows=1 width=881) (actual time=77.984..7,112.141 rows=103,524 loops=1)

3. 75.991 6,063.745 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.43..15,016.52 rows=1 width=844) (actual time=77.948..6,063.745 rows=103,524 loops=1)

  • Join Filter: (fsd.functional_structure_id = fs.id)
4. 89.359 5,884.230 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.29..15,016.35 rows=1 width=797) (actual time=77.941..5,884.230 rows=103,524 loops=1)

5. 1,025.039 5,691.347 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.15..15,016.14 rows=1 width=285) (actual time=77.933..5,691.347 rows=103,524 loops=1)

  • Join Filter: (sp.functional_structure_id = fsd.functional_structure_id)
  • Rows Removed by Join Filter: 8,385,444
6. 0.426 0.426 ↑ 1.0 82 1

Index Only Scan using pk_functional_structures_departments on functional_structures_departments fsd (cost=0.14..4.67 rows=82 width=8) (actual time=0.010..0.426 rows=82 loops=1)

  • Heap Fetches: 82
7. 2,704.983 4,665.882 ↓ 103,524.0 103,524 82

Materialize (cost=5,496.01..15,010.24 rows=1 width=277) (actual time=0.956..56.901 rows=103,524 loops=82)

8. 99.056 1,960.899 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.01..15,010.24 rows=1 width=277) (actual time=77.515..1,960.899 rows=103,524 loops=1)

9. 44.316 1,758.319 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,495.86..15,010.07 rows=1 width=256) (actual time=77.508..1,758.319 rows=103,524 loops=1)

10. 56.473 1,506.955 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,495.58..15,009.77 rows=1 width=211) (actual time=77.500..1,506.955 rows=103,524 loops=1)

11. 46.100 1,243.434 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,495.30..15,009.47 rows=1 width=169) (actual time=77.492..1,243.434 rows=103,524 loops=1)

12. 63.656 990.076 ↓ 103,629.0 103,629 1

Nested Loop (cost=5,495.02..15,009.18 rows=1 width=107) (actual time=77.482..990.076 rows=103,629 loops=1)

13. 124.315 719.162 ↓ 103,629.0 103,629 1

Nested Loop (cost=5,494.74..15,008.88 rows=1 width=53) (actual time=77.471..719.162 rows=103,629 loops=1)

14. 0.000 384.539 ↓ 105,154.0 105,154 1

Gather (cost=5,494.45..15,008.54 rows=1 width=45) (actual time=77.462..384.539 rows=105,154 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 61.525 972.113 ↓ 35,051.0 35,051 3 / 3

Nested Loop (cost=4,494.45..14,008.44 rows=1 width=45) (actual time=33.586..972.113 rows=35,051 loops=3)

16. 50.174 706.689 ↓ 101,949.0 101,949 3 / 3

Nested Loop (cost=4,494.16..14,008.10 rows=1 width=36) (actual time=33.189..706.689 rows=101,949 loops=3)

17. 122.267 452.616 ↓ 101,949.0 101,949 3 / 3

Nested Loop (cost=4,493.87..14,007.78 rows=1 width=32) (actual time=33.156..452.616 rows=101,949 loops=3)

18. 67.608 126.450 ↓ 101,949.0 101,949 3 / 3

Parallel Hash Join (cost=4,493.45..14,007.33 rows=1 width=24) (actual time=33.096..126.450 rows=101,949 loops=3)

  • Hash Cond: ((ar.sample_parameter_id = spl.sample_parameter_id) AND (ar.level_id = spl.id))
19. 27.425 27.425 ↑ 1.3 101,949 3 / 3

Parallel Seq Scan on analytical_results ar (cost=0.00..8,839.51 rows=128,451 width=20) (actual time=0.013..27.425 rows=101,949 loops=3)

20. 18.228 31.417 ↑ 1.8 55,212 3 / 3

Parallel Hash (cost=3,027.98..3,027.98 rows=97,698 width=12) (actual time=31.417..31.417 rows=55,212 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,888kB
21. 13.189 13.189 ↑ 1.8 55,212 3 / 3

Parallel Seq Scan on sample_parameter_levels spl (cost=0.00..3,027.98 rows=97,698 width=12) (actual time=0.021..13.189 rows=55,212 loops=3)

22. 203.899 203.899 ↑ 1.0 1 305,848 / 3

Index Scan using sample_parameters_pkey on sample_parameters sp (cost=0.42..0.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=305,848)

  • Index Cond: (id = ar.sample_parameter_id)
23. 203.899 203.899 ↑ 1.0 1 305,848 / 3

Index Scan using samples_pkey on samples s (cost=0.29..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=305,848)

  • Index Cond: (id = sp.sample_id)
24. 203.899 203.899 ↓ 0.0 0 305,848 / 3

Index Scan using work_orders_pkey on work_orders w (cost=0.29..0.34 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=305,848)

  • Index Cond: (id = s.work_order_id)
  • Filter: (active AND (received_at >= '2019-12-31 23:59:59'::timestamp without time zone) AND (received_at <= '2020-06-30 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 1
25. 210.308 210.308 ↑ 1.0 1 105,154

Index Scan using sample_registrations_pkey on sample_registrations sr (cost=0.29..0.34 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=105,154)

  • Index Cond: (id = s.registration_id)
  • Filter: active
  • Rows Removed by Filter: 0
26. 207.258 207.258 ↑ 1.0 1 103,629

Index Scan using analytical_parameters_pkey on analytical_parameters ap (cost=0.28..0.30 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=103,629)

  • Index Cond: (id = sp.parameter_id)
27. 207.258 207.258 ↑ 1.0 1 103,629

Index Scan using analytical_steps_pkey on analytical_steps ans (cost=0.28..0.30 rows=1 width=70) (actual time=0.002..0.002 rows=1 loops=103,629)

  • Index Cond: (id = spl.analytical_step_id)
28. 207.048 207.048 ↑ 1.0 1 103,524

Index Scan using result_definitions_pkey on result_definitions rd (cost=0.28..0.30 rows=1 width=50) (actual time=0.002..0.002 rows=1 loops=103,524)

  • Index Cond: (id = ar.definition_id)
29. 207.048 207.048 ↑ 1.0 1 103,524

Index Scan using result_definition_suites_pkey on result_definition_suites rds (cost=0.28..0.30 rows=1 width=53) (actual time=0.002..0.002 rows=1 loops=103,524)

  • Index Cond: (id = ar.definition_suite_id)
30. 103.524 103.524 ↑ 1.0 1 103,524

Index Scan using lab_matrices_pkey on lab_matrices lm (cost=0.15..0.17 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=103,524)

  • Index Cond: (id = sr.lab_matrix_id)
31. 103.524 103.524 ↑ 1.0 1 103,524

Index Scan using departments_pkey on departments d (cost=0.14..0.19 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=103,524)

  • Index Cond: (id = fsd.department_id)
32. 103.524 103.524 ↑ 1.0 1 103,524

Index Scan using functional_structures_pkey on functional_structures fs (cost=0.14..0.16 rows=1 width=55) (actual time=0.001..0.001 rows=1 loops=103,524)

  • Index Cond: (id = sp.functional_structure_id)
33. 207.048 207.048 ↑ 1.0 1 103,524

Index Scan using analytical_methods_pkey on analytical_methods am (cost=0.28..0.30 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=103,524)

  • Index Cond: (id = ar.analytical_method_id)
34.          

SubPlan (for Nested Loop Left Join)

35. 0.000 724.668 ↑ 1.0 1 103,524

Limit (cost=2.52..2.52 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=103,524)

36. 207.048 724.668 ↑ 1.0 1 103,524

Sort (cost=2.52..2.52 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=103,524)

  • Sort Key: technical_parameters.lab_matrix_id, technical_parameters.functional_structure_id, technical_parameters.response_time_days
  • Sort Method: quicksort Memory: 25kB
37. 517.620 517.620 ↑ 1.0 1 103,524

Index Only Scan using index_technical_params_on_method_param_matrix_and_fstruct_ids on technical_parameters (cost=0.29..2.51 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=103,524)

  • Index Cond: ((analytical_method_id = ar.analytical_method_id) AND (analytical_parameter_id = sp.parameter_id))
  • Filter: (((lab_matrix_id = sr.lab_matrix_id) OR (lab_matrix_id IS NULL)) AND ((functional_structure_id = sp.functional_structure_id) OR (functional_structure_id IS NULL)))
  • Rows Removed by Filter: 6
  • Heap Fetches: 709,418