explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lDQb

Settings
# exclusive inclusive rows x rows loops node
1. 11.606 18,458.009 ↑ 1.0 1 1

Result (cost=133,182.30..133,184.51 rows=1 width=1,249) (actual time=18,458.008..18,458.009 rows=1 loops=1)

2.          

Initplan (for Result)

3. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on result_boundary_types (cost=0.00..1.18 rows=1 width=516) (actual time=0.019..0.020 rows=1 loops=1)

  • Filter: (id = 8)
  • Rows Removed by Filter: 13
4. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on result_boundary_types result_boundary_types_1 (cost=0.00..1.18 rows=1 width=516) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: (id = 7)
  • Rows Removed by Filter: 13
5. 0.008 18,446.364 ↑ 1.0 1 1

Sort (cost=133,179.95..133,179.96 rows=1 width=3,325) (actual time=18,446.363..18,446.364 rows=1 loops=1)

  • Sort Key: (min(aplm."position"))
  • Sort Method: quicksort Memory: 25kB
6. 0.044 18,446.356 ↑ 1.0 1 1

GroupAggregate (cost=133,179.91..133,179.94 rows=1 width=3,325) (actual time=18,446.356..18,446.356 rows=1 loops=1)

  • Group Key: fs.id, cc.id, d.id, users.id
7. 0.016 18,446.312 ↓ 2.0 2 1

Sort (cost=133,179.91..133,179.92 rows=1 width=2,293) (actual time=18,446.312..18,446.312 rows=2 loops=1)

  • Sort Key: fs.id, cc.id, d.id, users.id
  • Sort Method: quicksort Memory: 26kB
8. 3.414 18,446.296 ↓ 2.0 2 1

Nested Loop Left Join (cost=2.30..133,179.90 rows=1 width=2,293) (actual time=1,223.506..18,446.296 rows=2 loops=1)

  • Join Filter: ((aplm.analytical_parameter_id = sp.parameter_id) AND (aplm.lab_matrix_id = sr.lab_matrix_id))
  • Rows Removed by Join Filter: 41564
9. 0.109 18,439.700 ↓ 2.0 2 1

Nested Loop Left Join (cost=2.30..132,547.33 rows=1 width=2,297) (actual time=1,223.013..18,439.700 rows=2 loops=1)

  • Join Filter: (users.id = arr.created_by_id)
  • Rows Removed by Join Filter: 732
10. 0.246 18,439.413 ↓ 2.0 2 1

Nested Loop (cost=2.30..132,524.91 rows=1 width=2,177) (actual time=1,222.858..18,439.413 rows=2 loops=1)

  • Join Filter: (last_ar.id = arr.result_id)
  • Rows Removed by Join Filter: 126
11. 0.332 2.623 ↓ 64.0 64 1

Seq Scan on analytical_result_validations arr (cost=0.00..85.79 rows=1 width=8) (actual time=0.037..2.623 rows=64 loops=1)

  • Filter: (updated_at = (SubPlan 6))
  • Rows Removed by Filter: 15
12.          

SubPlan (for Seq Scan)

13. 1.343 2.291 ↑ 1.0 1 79

Aggregate (cost=1.64..1.65 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=79)

14. 0.948 0.948 ↑ 1.0 1 79

Seq Scan on analytical_result_validations arr2 (cost=0.00..1.64 rows=1 width=8) (actual time=0.008..0.012 rows=1 loops=79)

  • Filter: (result_id = arr.result_id)
  • Rows Removed by Filter: 78
15. 154.560 18,436.544 ↑ 3,781.5 2 64

Nested Loop (cost=2.30..132,344.59 rows=7,563 width=2,177) (actual time=52.260..288.071 rows=2 loops=64)

  • Join Filter: (SubPlan 5)
  • Rows Removed by Join Filter: 90754
16. 1.152 33.536 ↓ 6.0 6 64

Nested Loop (cost=2.30..42.42 rows=1 width=2,177) (actual time=0.074..0.524 rows=6 loops=64)

17. 1.472 30.848 ↓ 6.0 6 64

Nested Loop (cost=2.17..42.10 rows=1 width=1,145) (actual time=0.068..0.482 rows=6 loops=64)

  • Join Filter: (sp.functional_structure_id = fs.id)
18. 1.152 27.840 ↓ 6.0 6 64

Nested Loop (cost=2.02..41.72 rows=1 width=1,060) (actual time=0.059..0.435 rows=6 loops=64)

19. 1.088 23.616 ↓ 6.0 6 64

Nested Loop (cost=1.88..39.55 rows=1 width=1,052) (actual time=0.051..0.369 rows=6 loops=64)

20. 0.576 20.224 ↓ 6.0 6 64

Nested Loop (cost=1.61..31.23 rows=1 width=1,052) (actual time=0.043..0.316 rows=6 loops=64)

21. 3.758 16.576 ↓ 6.0 6 64

Hash Join (cost=1.46..23.06 rows=1 width=1,052) (actual time=0.035..0.259 rows=6 loops=64)

  • Hash Cond: ((SubPlan 4) = d.id)
22. 1.280 1.280 ↑ 1.0 6 64

Index Scan using index_sample_parameters_on_sample_id_and_parameter_id on sample_parameters sp (cost=0.28..21.27 rows=6 width=16) (actual time=0.008..0.020 rows=6 loops=64)

  • Index Cond: (sample_id = 143)
23. 0.010 0.018 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=1,036) (actual time=0.018..0.018 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.008 0.008 ↑ 1.0 8 1

Seq Scan on departments d (cost=0.00..1.08 rows=8 width=1,036) (actual time=0.007..0.008 rows=8 loops=1)

25.          

SubPlan (for Hash Join)

26. 6.912 11.520 ↑ 1.0 1 768

Limit (cost=0.00..1.98 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=768)

27. 4.608 4.608 ↑ 1.0 1 768

Seq Scan on functional_structures_departments fsd_i (cost=0.00..1.98 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=768)

  • Filter: (functional_structure_id = sp.functional_structure_id)
  • Rows Removed by Filter: 33
28. 3.072 3.072 ↑ 1.0 1 384

Index Scan using samples_pkey on samples s (cost=0.15..8.17 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=384)

  • Index Cond: (id = 143)
29. 2.304 2.304 ↑ 1.0 1 384

Index Scan using sample_registrations_pkey on sample_registrations sr (cost=0.28..8.29 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=384)

  • Index Cond: (id = s.registration_id)
30. 3.072 3.072 ↑ 1.0 1 384

Index Only Scan using pk_functional_structures_cost_centers on functional_structures_cost_centers fscc (cost=0.14..2.16 rows=1 width=8) (actual time=0.005..0.008 rows=1 loops=384)

  • Index Cond: (functional_structure_id = sp.functional_structure_id)
  • Heap Fetches: 384
31. 1.536 1.536 ↑ 1.0 1 384

Index Scan using functional_structures_pkey on functional_structures fs (cost=0.14..0.37 rows=1 width=93) (actual time=0.004..0.004 rows=1 loops=384)

  • Index Cond: (id = fscc.functional_structure_id)
32. 1.536 1.536 ↑ 1.0 1 384

Index Scan using cost_centers_pkey on cost_centers cc (cost=0.14..0.31 rows=1 width=1,036) (actual time=0.004..0.004 rows=1 loops=384)

  • Index Cond: (id = fscc.cost_center_id)
33. 823.296 823.296 ↑ 1.0 15,126 384

Seq Scan on analytical_results last_ar (cost=0.00..460.26 rows=15,126 width=4) (actual time=0.004..2.144 rows=15,126 loops=384)

34.          

SubPlan (for Nested Loop)

35. 5,808.384 17,425.152 ↓ 0.0 0 5,808,384

Sort (cost=8.70..8.70 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=5,808,384)

  • Sort Key: ar_i.value_at DESC
  • Sort Method: quicksort Memory: 25kB
36. 11,616.768 11,616.768 ↓ 0.0 0 5,808,384

Index Scan using index_analytical_results_sample_parameter_id on analytical_results ar_i (cost=0.29..8.69 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=5,808,384)

  • Index Cond: (sample_parameter_id = sp.id)
  • Filter: ((definition_suite_id IS NOT NULL) AND (value_id IS NOT NULL) AND ((state)::text <> ALL ('{repeated,cancelled,unarrived}'::text[])))
  • Rows Removed by Filter: 1
37. 0.178 0.178 ↑ 1.0 367 2

Seq Scan on users (cost=0.00..17.74 rows=374 width=124) (actual time=0.015..0.089 rows=367 loops=2)

38. 3.182 3.182 ↑ 1.0 20,783 2

Seq Scan on analytical_parameters_lab_matrices aplm (cost=0.00..320.83 rows=20,783 width=12) (actual time=0.017..1.591 rows=20,783 loops=2)

39.          

SubPlan (for Result)

40. 0.003 0.014 ↑ 1.0 1 1

Aggregate (cost=1.18..1.19 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)

41. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on functional_structures_notes (cost=0.00..1.18 rows=1 width=32) (actual time=0.010..0.011 rows=0 loops=1)

  • Filter: (((type)::text = 'workOrder'::text) AND (type_id = 142) AND (functional_structure_id = fs.id))
  • Rows Removed by Filter: 10
Planning time : 5.599 ms
Execution time : 18,458.438 ms