explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 03Oj

Settings
# exclusive inclusive rows x rows loops node
1. 157.768 6,418.015 ↓ 103,524.0 103,524 1

Nested Loop Left Join (cost=5,496.71..15,019.34 rows=1 width=869) (actual time=71.732..6,418.015 rows=103,524 loops=1)

2. 67.859 5,432.055 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.43..15,016.52 rows=1 width=832) (actual time=71.689..5,432.055 rows=103,524 loops=1)

  • Join Filter: (fsd.functional_structure_id = fs.id)
3. 76.974 5,260.672 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.29..15,016.35 rows=1 width=785) (actual time=71.678..5,260.672 rows=103,524 loops=1)

4. 885.033 5,080.174 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.15..15,016.14 rows=1 width=273) (actual time=71.665..5,080.174 rows=103,524 loops=1)

  • Join Filter: (sp.functional_structure_id = fsd.functional_structure_id)
  • Rows Removed by Join Filter: 8,385,444
5. 0.349 0.349 ↑ 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.349 rows=82 loops=1)

  • Heap Fetches: 82
6. 2,497.423 4,194.792 ↓ 103,524.0 103,524 82

Materialize (cost=5,496.01..15,010.24 rows=1 width=265) (actual time=0.877..51.156 rows=103,524 loops=82)

7. 72.005 1,697.369 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,496.01..15,010.24 rows=1 width=265) (actual time=71.149..1,697.369 rows=103,524 loops=1)

8. 10.206 1,521.840 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,495.86..15,010.07 rows=1 width=244) (actual time=71.141..1,521.840 rows=103,524 loops=1)

9. 20.530 1,304.586 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,495.58..15,009.77 rows=1 width=199) (actual time=71.129..1,304.586 rows=103,524 loops=1)

10. 7.188 1,077.008 ↓ 103,524.0 103,524 1

Nested Loop (cost=5,495.30..15,009.47 rows=1 width=157) (actual time=71.115..1,077.008 rows=103,524 loops=1)

11. 20.683 862.562 ↓ 103,629.0 103,629 1

Nested Loop (cost=5,495.02..15,009.18 rows=1 width=95) (actual time=71.104..862.562 rows=103,629 loops=1)

12. 61.940 634.621 ↓ 103,629.0 103,629 1

Nested Loop (cost=5,494.74..15,008.88 rows=1 width=41) (actual time=71.089..634.621 rows=103,629 loops=1)

13. 0.000 362.373 ↓ 105,154.0 105,154 1

Gather (cost=5,494.45..15,008.54 rows=1 width=33) (actual time=71.076..362.373 rows=105,154 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 43.125 885.649 ↓ 35,051.0 35,051 3 / 3

Nested Loop (cost=4,494.45..14,008.44 rows=1 width=33) (actual time=28.900..885.649 rows=35,051 loops=3)

15. 25.091 638.625 ↓ 101,949.0 101,949 3 / 3

Nested Loop (cost=4,494.16..14,008.10 rows=1 width=32) (actual time=28.631..638.625 rows=101,949 loops=3)

16. 92.795 409.635 ↓ 101,949.0 101,949 3 / 3

Nested Loop (cost=4,493.87..14,007.78 rows=1 width=28) (actual time=28.615..409.635 rows=101,949 loops=3)

17. 63.227 112.941 ↓ 101,949.0 101,949 3 / 3

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

  • Hash Cond: ((ar.sample_parameter_id = spl.sample_parameter_id) AND (ar.level_id = spl.id))
18. 22.571 22.571 ↑ 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.010..22.571 rows=101,949 loops=3)

19. 15.968 27.143 ↑ 1.8 55,212 3 / 3

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,856kB
20. 11.175 11.175 ↑ 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.019..11.175 rows=55,212 loops=3)

21. 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)
22. 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)
23. 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=9) (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
24. 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
25. 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)
26. 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)
27. 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)
28. 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)
29. 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)
30. 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)
31. 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)
32. 103.524 103.524 ↑ 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.001..0.001 rows=1 loops=103,524)

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

SubPlan (for Nested Loop Left Join)

34. 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)

35. 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
36. 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.003..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