explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nxKG

Settings
# exclusive inclusive rows x rows loops node
1. 55.162 644.598 ↓ 13,759.0 13,759 1

Sort (cost=9,202.92..9,202.92 rows=1 width=881) (actual time=639.308..644.598 rows=13,759 loops=1)

  • Sort Key: w.created_at DESC, w.number DESC, sp.id DESC
  • Sort Method: external merge Disk: 5,856kB
2. 22.192 589.436 ↓ 13,759.0 13,759 1

Nested Loop Left Join (cost=1,003.81..9,202.91 rows=1 width=881) (actual time=6.459..589.436 rows=13,759 loops=1)

3. 9.184 457.172 ↓ 13,759.0 13,759 1

Nested Loop (cost=1,003.53..9,200.09 rows=1 width=844) (actual time=6.419..457.172 rows=13,759 loops=1)

  • Join Filter: (fsd.functional_structure_id = fs.id)
4. 8.821 434.229 ↓ 13,759.0 13,759 1

Nested Loop (cost=1,003.39..9,199.91 rows=1 width=797) (actual time=6.405..434.229 rows=13,759 loops=1)

5. 122.720 411.649 ↓ 13,759.0 13,759 1

Nested Loop (cost=1,003.25..9,199.71 rows=1 width=285) (actual time=6.396..411.649 rows=13,759 loops=1)

  • Join Filter: (sp.functional_structure_id = fsd.functional_structure_id)
  • Rows Removed by Join Filter: 1,114,479
6. 0.125 0.125 ↑ 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.125 rows=82 loops=1)

  • Heap Fetches: 82
7. 60.958 288.804 ↓ 13,759.0 13,759 82

Materialize (cost=1,003.11..9,193.81 rows=1 width=277) (actual time=0.029..3.522 rows=13,759 loops=82)

8. 12.369 227.846 ↓ 13,759.0 13,759 1

Nested Loop (cost=1,003.11..9,193.81 rows=1 width=277) (actual time=2.362..227.846 rows=13,759 loops=1)

9. 5.802 201.718 ↓ 13,759.0 13,759 1

Nested Loop (cost=1,002.96..9,193.64 rows=1 width=256) (actual time=2.355..201.718 rows=13,759 loops=1)

10. 6.498 168.398 ↓ 13,759.0 13,759 1

Nested Loop (cost=1,002.68..9,193.34 rows=1 width=211) (actual time=2.350..168.398 rows=13,759 loops=1)

11. 5.013 134.382 ↓ 13,759.0 13,759 1

Nested Loop (cost=1,002.40..9,193.04 rows=1 width=169) (actual time=2.344..134.382 rows=13,759 loops=1)

12. 7.116 101.641 ↓ 13,864.0 13,864 1

Nested Loop (cost=1,002.12..9,192.74 rows=1 width=107) (actual time=2.336..101.641 rows=13,864 loops=1)

13. 11.265 66.797 ↓ 13,864.0 13,864 1

Nested Loop (cost=1,001.84..9,192.44 rows=1 width=53) (actual time=2.329..66.797 rows=13,864 loops=1)

14. 0.000 27.378 ↓ 14,077.0 14,077 1

Gather (cost=1,001.55..9,192.10 rows=1 width=45) (actual time=2.323..27.378 rows=14,077 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
15. 3.995 48.144 ↓ 7,039.0 7,039 2 / 2

Nested Loop (cost=1.55..8,192.00 rows=1 width=45) (actual time=0.132..48.144 rows=7,039 loops=2)

16. 2.882 30.072 ↓ 1.1 7,039 2 / 2

Nested Loop (cost=1.13..5,341.47 rows=6,211 width=49) (actual time=0.114..30.072 rows=7,039 loops=2)

17. 2.017 16.382 ↓ 1.1 3,603 2 / 2

Nested Loop (cost=0.71..3,274.78 rows=3,358 width=29) (actual time=0.094..16.382 rows=3,603 loops=2)

18. 0.583 9.717 ↓ 1.1 1,162 2 / 2

Nested Loop (cost=0.29..2,592.75 rows=1,017 width=21) (actual time=0.067..9.717 rows=1,162 loops=2)

19. 6.988 6.988 ↓ 1.1 716 2 / 2

Parallel Seq Scan on work_orders w (cost=0.00..1,590.60 rows=656 width=17) (actual time=0.037..6.988 rows=716 loops=2)

  • Filter: (active AND (received_at >= '2020-02-29 23:59:59'::timestamp without time zone) AND (received_at <= '2020-06-30 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 15,547
20. 2.147 2.147 ↑ 1.0 2 1,431 / 2

Index Scan using index_samples_on_work_order_id_and_position on samples s (cost=0.29..1.51 rows=2 width=12) (actual time=0.003..0.003 rows=2 loops=1,431)

  • Index Cond: (work_order_id = w.id)
21. 4.648 4.648 ↑ 2.0 3 2,324 / 2

Index Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters sp (cost=0.42..0.61 rows=6 width=16) (actual time=0.003..0.004 rows=3 loops=2,324)

  • Index Cond: (sample_id = s.id)
22. 10.808 10.808 ↑ 2.5 2 7,205 / 2

Index Scan using index_analytical_results_sample_parameter_id on analytical_results ar (cost=0.42..0.57 rows=5 width=20) (actual time=0.003..0.003 rows=2 loops=7,205)

  • Index Cond: (sample_parameter_id = sp.id)
23. 14.077 14.077 ↑ 1.0 1 14,077 / 2

Index Scan using sample_parameter_levels_pkey on sample_parameter_levels spl (cost=0.42..0.45 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=14,077)

  • Index Cond: (id = ar.level_id)
  • Filter: (ar.sample_parameter_id = sample_parameter_id)
24. 28.154 28.154 ↑ 1.0 1 14,077

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=14,077)

  • Index Cond: (id = s.registration_id)
  • Filter: active
  • Rows Removed by Filter: 0
25. 27.728 27.728 ↑ 1.0 1 13,864

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=13,864)

  • Index Cond: (id = sp.parameter_id)
26. 27.728 27.728 ↑ 1.0 1 13,864

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=13,864)

  • Index Cond: (id = spl.analytical_step_id)
27. 27.518 27.518 ↑ 1.0 1 13,759

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=13,759)

  • Index Cond: (id = ar.definition_id)
28. 27.518 27.518 ↑ 1.0 1 13,759

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=13,759)

  • Index Cond: (id = ar.definition_suite_id)
29. 13.759 13.759 ↑ 1.0 1 13,759

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=13,759)

  • Index Cond: (id = sr.lab_matrix_id)
30. 13.759 13.759 ↑ 1.0 1 13,759

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=13,759)

  • Index Cond: (id = fsd.department_id)
31. 13.759 13.759 ↑ 1.0 1 13,759

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=13,759)

  • Index Cond: (id = sp.functional_structure_id)
32. 13.759 13.759 ↑ 1.0 1 13,759

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=13,759)

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

SubPlan (for Nested Loop Left Join)

34. 0.000 96.313 ↑ 1.0 1 13,759

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

35. 27.518 96.313 ↑ 1.0 1 13,759

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

  • Sort Key: technical_parameters.lab_matrix_id, technical_parameters.functional_structure_id, technical_parameters.response_time_days
  • Sort Method: quicksort Memory: 25kB
36. 68.795 68.795 ↑ 1.0 1 13,759

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=13,759)

  • 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: 102,035