explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NPYB

Settings
# exclusive inclusive rows x rows loops node
1. 13.937 25,490.959 ↓ 63.2 2,716 1

Sort (cost=5,567.91..5,568.02 rows=43 width=481) (actual time=25,486.922..25,490.959 rows=2,716 loops=1)

  • Sort Key: analytical_reports.serial DESC
  • Sort Method: quicksort Memory: 791kB
2.          

CTE q

3. 0.423 25,263.141 ↓ 12.5 100 1

Limit (cost=5,146.91..5,146.93 rows=8 width=8) (actual time=25,262.549..25,263.141 rows=100 loops=1)

4. 12.918 25,262.718 ↓ 12.5 100 1

Sort (cost=5,146.91..5,146.93 rows=8 width=8) (actual time=25,262.541..25,262.718 rows=100 loops=1)

  • Sort Key: analytical_reports_1.serial DESC
  • Sort Method: top-N heapsort Memory: 34kB
5. 182.218 25,249.800 ↓ 997.2 7,978 1

Group (cost=5,146.75..5,146.79 rows=8 width=8) (actual time=24,879.194..25,249.800 rows=7,978 loops=1)

  • Group Key: analytical_reports_1.id
6. 1,087.167 25,067.582 ↓ 16,007.8 128,062 1

Sort (cost=5,146.75..5,146.77 rows=8 width=8) (actual time=24,879.182..25,067.582 rows=128,062 loops=1)

  • Sort Key: analytical_reports_1.id
  • Sort Method: external merge Disk: 2272kB
7. 1,740.276 23,980.415 ↓ 16,007.8 128,062 1

Nested Loop (cost=2,566.60..5,146.63 rows=8 width=8) (actual time=155.051..23,980.415 rows=128,062 loops=1)

8. 1,090.486 20,319.209 ↓ 18,294.6 128,062 1

Hash Left Join (cost=2,566.33..5,144.54 rows=7 width=12) (actual time=154.991..20,319.209 rows=128,062 loops=1)

  • Hash Cond: (analytical_reports_1.id = t07_1.analytical_report_id)
9. 1,501.405 19,221.552 ↓ 17,335.7 121,350 1

Nested Loop Left Join (cost=2,491.25..5,069.39 rows=7 width=12) (actual time=147.786..19,221.552 rows=121,350 loops=1)

10. 1,308.086 8,376.197 ↓ 17,335.7 121,350 1

Nested Loop Left Join (cost=2,491.25..4,849.95 rows=7 width=54) (actual time=147.540..8,376.197 rows=121,350 loops=1)

11. 1,352.283 6,704.061 ↓ 17,335.7 121,350 1

Nested Loop Left Join (cost=2,491.10..4,848.14 rows=7 width=16) (actual time=147.525..6,704.061 rows=121,350 loops=1)

12. 1,243.832 4,138.278 ↓ 17,335.7 121,350 1

Nested Loop (cost=2,490.83..4,846.03 rows=7 width=16) (actual time=147.458..4,138.278 rows=121,350 loops=1)

13. 245.620 1,477.316 ↓ 12,883.0 25,766 1

Nested Loop (cost=2,490.42..4,838.89 rows=2 width=16) (actual time=147.428..1,477.316 rows=25,766 loops=1)

14. 289.815 1,000.247 ↓ 7,981.0 7,981 1

Nested Loop Left Join (cost=2,490.13..4,838.08 rows=1 width=24) (actual time=147.393..1,000.247 rows=7,981 loops=1)

15. 116.586 622.641 ↓ 7,981.0 7,981 1

Nested Loop (cost=2,489.85..4,837.29 rows=1 width=20) (actual time=147.374..622.641 rows=7,981 loops=1)

16. 47.268 338.454 ↓ 7,981.0 7,981 1

Hash Join (cost=2,489.56..4,836.94 rows=1 width=16) (actual time=147.314..338.454 rows=7,981 loops=1)

  • Hash Cond: (analytical_reports_1.work_order_id = work_orders_last_edition_1.id)
  • Join Filter: (((analytical_reports_1.sample_id IS NULL) AND (analytical_reports_1.serial = work_orders_last_edition_1.report_serial) AND (analytical_reports_1.edition = work_orders_last_edition_1.report_edition)) OR ((analytical_reports_1.sample_id IS NOT NULL) AND (analytical_reports_1.serial = samples_last_edition.report_serial) AND (analytical_reports_1.edition = samples_last_edition.report_edition)))
  • Rows Removed by Join Filter: 1127
17. 73.219 214.008 ↓ 1.3 9,108 1

Hash Left Join (cost=1,455.19..3,783.92 rows=7,105 width=28) (actual time=69.973..214.008 rows=9,108 loops=1)

  • Hash Cond: (analytical_reports_1.sample_id = samples_last_edition.id)
18. 71.213 71.213 ↓ 1.3 9,108 1

Seq Scan on analytical_reports analytical_reports_1 (cost=0.00..2,310.07 rows=7,105 width=20) (actual time=0.161..71.213 rows=9,108 loops=1)

  • Filter: ((NOT preview) AND ((sample_id IS NULL) OR (sample_id IS NOT NULL)) AND (tenant_id = 1))
  • Rows Removed by Filter: 21285
19. 30.556 69.576 ↓ 1.0 16,382 1

Hash (cost=1,250.64..1,250.64 rows=16,364 width=12) (actual time=69.572..69.576 rows=16,382 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 719kB
20. 39.020 39.020 ↓ 1.0 16,382 1

Seq Scan on samples samples_last_edition (cost=0.00..1,250.64 rows=16,364 width=12) (actual time=0.025..39.020 rows=16,382 loops=1)

21. 33.011 77.178 ↓ 1.0 10,868 1

Hash (cost=898.61..898.61 rows=10,861 width=12) (actual time=77.176..77.178 rows=10,868 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 558kB
22. 44.167 44.167 ↓ 1.0 10,868 1

Seq Scan on work_orders work_orders_last_edition_1 (cost=0.00..898.61 rows=10,861 width=12) (actual time=0.047..44.167 rows=10,868 loops=1)

23. 167.601 167.601 ↑ 1.0 1 7,981

Index Only Scan using work_orders_pkey on work_orders work_order_filter_1 (cost=0.29..0.35 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=7,981)

  • Index Cond: (id = work_orders_last_edition_1.id)
  • Heap Fetches: 1988
24. 87.791 87.791 ↑ 1.0 1 7,981

Index Scan using work_orders_pkey on work_orders t01_1 (cost=0.29..0.79 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=7,981)

  • Index Cond: (id = analytical_reports_1.work_order_id)
25. 231.449 231.449 ↓ 1.5 3 7,981

Index Scan using index_samples_on_work_order_id_state on samples samples_filter_1 (cost=0.29..0.80 rows=2 width=8) (actual time=0.014..0.029 rows=3 loops=7,981)

  • Index Cond: (work_order_id = work_orders_last_edition_1.id)
26. 1,417.130 1,417.130 ↓ 1.2 5 25,766

Index Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters sample_parameters_filter_1 (cost=0.41..3.53 rows=4 width=8) (actual time=0.020..0.055 rows=5 loops=25,766)

  • Index Cond: (sample_id = samples_filter_1.id)
27. 1,213.500 1,213.500 ↓ 0.0 0 121,350

Index Only Scan using work_order_collecting_method_pkey on work_order_collecting_method wocm_1 (cost=0.27..0.29 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=121,350)

  • Index Cond: (work_order_id = t01_1.id)
  • Heap Fetches: 342
28. 364.050 364.050 ↓ 0.0 0 121,350

Index Scan using master_types_pkey on master_types t04_1 (cost=0.14..0.26 rows=1 width=46) (actual time=0.003..0.003 rows=0 loops=121,350)

  • Index Cond: (id = wocm_1.collecting_method_id)
29. 5,582.100 9,343.950 ↓ 0.0 0 121,350

Append (cost=0.00..31.26 rows=9 width=157) (actual time=0.077..0.077 rows=0 loops=121,350)

30. 485.400 485.400 ↓ 0.0 0 121,350

Seq Scan on translations t05_9 (cost=0.00..0.00 rows=1 width=516) (actual time=0.004..0.004 rows=0 loops=121,350)

  • Filter: ((t04_1.translation_key)::text = (key)::text)
31. 364.050 364.050 ↓ 0.0 0 121,350

Index Only Scan using translations_de_de_pk on "translations_de-DE" t05_10 (cost=0.14..1.34 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
32. 485.400 485.400 ↓ 0.0 0 121,350

Index Only Scan using translations_en_gb_pk on "translations_en-GB" t05_11 (cost=0.14..1.34 rows=1 width=516) (actual time=0.004..0.004 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
33. 364.050 364.050 ↓ 0.0 0 121,350

Index Only Scan using translations_en_us_pk on "translations_en-US" t05_12 (cost=0.14..1.34 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
34. 485.400 485.400 ↓ 0.0 0 121,350

Index Only Scan using translations_es_es_pk on "translations_es-ES" t05_13 (cost=0.14..1.34 rows=1 width=516) (actual time=0.004..0.004 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
35. 364.050 364.050 ↓ 0.0 0 121,350

Index Only Scan using translations_fr_fr_pk on "translations_fr-FR" t05_14 (cost=0.14..1.34 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
36. 242.700 242.700 ↓ 0.0 0 121,350

Index Only Scan using translations_pt_ao_pk on "translations_pt-AO" t05_15 (cost=0.14..1.34 rows=1 width=516) (actual time=0.002..0.002 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
37. 485.400 485.400 ↓ 0.0 0 121,350

Index Only Scan using translations_pt_br_pk on "translations_pt-BR" t05_16 (cost=0.14..1.34 rows=1 width=516) (actual time=0.004..0.004 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
38. 485.400 485.400 ↓ 0.0 0 121,350

Index Only Scan using translations_pt_pt_pk on "translations_pt-PT" t05_17 (cost=0.28..21.86 rows=1 width=37) (actual time=0.004..0.004 rows=0 loops=121,350)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 342
39. 3.742 7.171 ↓ 1.0 2,100 1

Hash (cost=49.48..49.48 rows=2,048 width=4) (actual time=7.168..7.171 rows=2,100 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 106kB
40. 3.429 3.429 ↓ 1.0 2,100 1

Seq Scan on analytical_report_publications t07_1 (cost=0.00..49.48 rows=2,048 width=4) (actual time=0.033..3.429 rows=2,100 loops=1)

41. 1,920.930 1,920.930 ↑ 1.0 1 128,062

Index Only Scan using pk_functional_structures_users on functional_structures_users fsu_filter (cost=0.28..0.30 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=128,062)

  • Index Cond: ((functional_structure_id = sample_parameters_filter_1.functional_structure_id) AND (user_id = 1))
  • Heap Fetches: 128062
42. 18.060 25,477.022 ↓ 63.2 2,716 1

Nested Loop Left Join (cost=182.38..419.81 rows=43 width=481) (actual time=25,373.101..25,477.022 rows=2,716 loops=1)

43. 19.347 25,439.950 ↓ 63.2 2,716 1

Nested Loop Left Join (cost=181.97..392.15 rows=43 width=469) (actual time=25,373.060..25,439.950 rows=2,716 loops=1)

44. 19.661 25,415.171 ↓ 63.2 2,716 1

Nested Loop Left Join (cost=181.68..369.09 rows=43 width=443) (actual time=25,373.044..25,415.171 rows=2,716 loops=1)

45. 11.381 25,381.930 ↓ 63.2 2,716 1

Hash Right Join (cost=181.40..352.05 rows=43 width=407) (actual time=25,372.997..25,381.930 rows=2,716 loops=1)

  • Hash Cond: ((t05.key)::text = (t04.translation_key)::text)
46. 6.540 10.798 ↑ 1.5 2,374 1

Append (cost=0.00..156.57 rows=3,639 width=178) (actual time=0.152..10.798 rows=2,374 loops=1)

47. 0.033 0.033 ↓ 0.0 0 1

Seq Scan on translations t05 (cost=0.00..0.00 rows=1 width=548) (actual time=0.032..0.033 rows=0 loops=1)

48. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on "translations_de-DE" t05_1 (cost=0.00..11.30 rows=130 width=548) (actual time=0.009..0.010 rows=0 loops=1)

49. 0.019 0.019 ↓ 0.0 0 1

Seq Scan on "translations_en-GB" t05_2 (cost=0.00..11.30 rows=130 width=548) (actual time=0.018..0.019 rows=0 loops=1)

50. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on "translations_en-US" t05_3 (cost=0.00..11.30 rows=130 width=548) (actual time=0.007..0.009 rows=0 loops=1)

51. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on "translations_es-ES" t05_4 (cost=0.00..11.30 rows=130 width=548) (actual time=0.008..0.009 rows=0 loops=1)

52. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on "translations_fr-FR" t05_5 (cost=0.00..11.30 rows=130 width=548) (actual time=0.008..0.009 rows=0 loops=1)

53. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on "translations_pt-AO" t05_6 (cost=0.00..11.30 rows=130 width=548) (actual time=0.010..0.012 rows=0 loops=1)

54. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on "translations_pt-BR" t05_7 (cost=0.00..11.30 rows=130 width=548) (actual time=0.008..0.009 rows=0 loops=1)

55. 4.148 4.148 ↑ 1.1 2,374 1

Seq Scan on "translations_pt-PT" t05_8 (cost=0.00..59.28 rows=2,728 width=54) (actual time=0.032..4.148 rows=2,374 loops=1)

56. 8.715 25,359.751 ↓ 63.2 2,716 1

Hash (cost=180.87..180.87 rows=43 width=229) (actual time=25,359.749..25,359.751 rows=2,716 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 315kB
57. 13.673 25,351.036 ↓ 63.2 2,716 1

Hash Left Join (cost=123.56..180.87 rows=43 width=229) (actual time=25,326.864..25,351.036 rows=2,716 loops=1)

  • Hash Cond: (wocm.collecting_method_id = t04.id)
58. 14.647 25,336.065 ↓ 63.2 2,716 1

Hash Right Join (cost=115.77..172.96 rows=43 width=152) (actual time=25,325.499..25,336.065 rows=2,716 loops=1)

  • Hash Cond: (t07.analytical_report_id = analytical_reports.id)
59. 6.473 6.473 ↓ 1.0 2,100 1

Seq Scan on analytical_report_publications t07 (cost=0.00..49.48 rows=2,048 width=69) (actual time=0.035..6.473 rows=2,100 loops=1)

60. 5.888 25,314.945 ↓ 63.2 2,716 1

Hash (cost=115.23..115.23 rows=43 width=83) (actual time=25,314.943..25,314.945 rows=2,716 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 313kB
61. 5.700 25,309.057 ↓ 63.2 2,716 1

Hash Right Join (cost=105.34..115.23 rows=43 width=83) (actual time=25,303.051..25,309.057 rows=2,716 loops=1)

  • Hash Cond: (wocm.work_order_id = t01.id)
62. 0.391 0.391 ↑ 1.1 198 1

Seq Scan on work_order_collecting_method wocm (cost=0.00..9.10 rows=210 width=8) (actual time=0.042..0.391 rows=198 loops=1)

63. 7.616 25,302.966 ↓ 63.2 2,716 1

Hash (cost=104.80..104.80 rows=43 width=79) (actual time=25,302.965..25,302.966 rows=2,716 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 313kB
64. 12.041 25,295.350 ↓ 63.2 2,716 1

Nested Loop (cost=1.84..104.80 rows=43 width=79) (actual time=25,263.034..25,295.350 rows=2,716 loops=1)

65. 1.891 25,272.509 ↓ 36.0 432 1

Nested Loop (cost=1.43..79.18 rows=12 width=83) (actual time=25,262.995..25,272.509 rows=432 loops=1)

  • Join Filter: (work_orders_last_edition.id = samples_filter.work_order_id)
66. 0.974 25,268.618 ↓ 12.5 100 1

Nested Loop Left Join (cost=1.14..75.04 rows=8 width=91) (actual time=25,262.961..25,268.618 rows=100 loops=1)

67. 0.552 25,267.144 ↓ 12.5 100 1

Nested Loop (cost=0.86..71.71 rows=8 width=75) (actual time=25,262.936..25,267.144 rows=100 loops=1)

68. 0.565 25,266.192 ↓ 12.5 100 1

Nested Loop (cost=0.57..69.16 rows=8 width=71) (actual time=25,262.914..25,266.192 rows=100 loops=1)

69. 0.530 25,265.027 ↓ 12.5 100 1

Nested Loop (cost=0.29..66.60 rows=8 width=67) (actual time=25,262.848..25,265.027 rows=100 loops=1)

70. 25,263.497 25,263.497 ↓ 12.5 100 1

CTE Scan on q (cost=0.00..0.16 rows=8 width=4) (actual time=25,262.561..25,263.497 rows=100 loops=1)

71. 1.000 1.000 ↑ 1.0 1 100

Index Scan using analytical_reports_pkey on analytical_reports (cost=0.29..8.30 rows=1 width=67) (actual time=0.010..0.010 rows=1 loops=100)

  • Index Cond: (id = q."Id")
72. 0.600 0.600 ↑ 1.0 1 100

Index Only Scan using work_orders_pkey on work_orders work_order_filter (cost=0.29..0.32 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100)

  • Index Cond: (id = analytical_reports.work_order_id)
  • Heap Fetches: 38
73. 0.400 0.400 ↑ 1.0 1 100

Index Only Scan using work_orders_pkey on work_orders work_orders_last_edition (cost=0.29..0.32 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (id = analytical_reports.work_order_id)
  • Heap Fetches: 38
74. 0.500 0.500 ↑ 1.0 1 100

Index Scan using work_orders_pkey on work_orders t01 (cost=0.29..0.42 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=100)

  • Index Cond: (id = analytical_reports.work_order_id)
75. 2.000 2.000 ↓ 2.0 4 100

Index Scan using index_samples_on_work_order_id_state on samples samples_filter (cost=0.29..0.49 rows=2 width=8) (actual time=0.008..0.020 rows=4 loops=100)

  • Index Cond: (work_order_id = analytical_reports.work_order_id)
76. 10.800 10.800 ↓ 1.5 6 432

Index Only Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters sample_parameters_filter (cost=0.41..2.09 rows=4 width=4) (actual time=0.008..0.025 rows=6 loops=432)

  • Index Cond: (sample_id = samples_filter.id)
  • Heap Fetches: 2009
77. 0.668 1.298 ↑ 1.5 144 1

Hash (cost=5.13..5.13 rows=213 width=81) (actual time=1.296..1.298 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
78. 0.630 0.630 ↑ 1.5 144 1

Seq Scan on master_types t04 (cost=0.00..5.13 rows=213 width=81) (actual time=0.037..0.630 rows=144 loops=1)

79. 13.580 13.580 ↑ 1.0 1 2,716

Index Scan using entities_pkey on entities t02 (cost=0.28..0.40 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=2,716)

  • Index Cond: (t01.customer_id = id)
80. 5.432 5.432 ↓ 0.0 0 2,716

Index Scan using patients_pk on patients t03 (cost=0.29..0.54 rows=1 width=30) (actual time=0.002..0.002 rows=0 loops=2,716)

  • Index Cond: (t01.patient_id = id)
81. 19.012 19.012 ↑ 1.0 1 2,716

Index Scan using samples_pkey on samples t06 (cost=0.41..0.64 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=2,716)

  • Index Cond: (id = analytical_reports.sample_id)
Planning time : 42.827 ms
Execution time : 25,501.770 ms