explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tyWN

Settings
# exclusive inclusive rows x rows loops node
1. 13.687 11,852.649 ↓ 61.2 2,632 1

Sort (cost=5,711.59..5,711.69 rows=43 width=481) (actual time=11,848.818..11,852.649 rows=2,632 loops=1)

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

CTE q

3. 0.425 11,673.874 ↓ 12.5 100 1

Limit (cost=5,289.97..5,289.99 rows=8 width=8) (actual time=11,673.289..11,673.874 rows=100 loops=1)

4. 15.402 11,673.449 ↓ 12.5 100 1

Sort (cost=5,289.97..5,289.99 rows=8 width=8) (actual time=11,673.284..11,673.449 rows=100 loops=1)

  • Sort Key: analytical_reports_1.serial DESC
  • Sort Method: top-N heapsort Memory: 34kB
5. 210.799 11,658.047 ↓ 998.8 7,990 1

Group (cost=5,289.81..5,289.85 rows=8 width=8) (actual time=11,242.890..11,658.047 rows=7,990 loops=1)

  • Group Key: analytical_reports_1.id
6. 503.122 11,447.248 ↓ 16,016.9 128,135 1

Sort (cost=5,289.81..5,289.83 rows=8 width=8) (actual time=11,242.879..11,447.248 rows=128,135 loops=1)

  • Sort Key: analytical_reports_1.id
  • Sort Method: external merge Disk: 2272kB
7. 728.043 10,944.126 ↓ 16,016.9 128,135 1

Nested Loop (cost=2,804.18..5,289.69 rows=8 width=8) (actual time=130.621..10,944.126 rows=128,135 loops=1)

8. 471.734 9,447.273 ↓ 18,305.0 128,135 1

Hash Left Join (cost=2,803.91..5,287.60 rows=7 width=12) (actual time=130.558..9,447.273 rows=128,135 loops=1)

  • Hash Cond: (analytical_reports_1.id = t07_1.analytical_report_id)
9. 691.674 8,968.550 ↓ 17,346.1 121,423 1

Nested Loop Left Join (cost=2,728.83..5,212.45 rows=7 width=12) (actual time=123.528..8,968.550 rows=121,423 loops=1)

10. 571.620 3,784.225 ↓ 17,346.1 121,423 1

Nested Loop Left Join (cost=2,728.83..4,993.00 rows=7 width=54) (actual time=123.321..3,784.225 rows=121,423 loops=1)

11. 651.114 2,969.759 ↓ 17,346.1 121,423 1

Nested Loop Left Join (cost=2,728.68..4,991.20 rows=7 width=16) (actual time=123.308..2,969.759 rows=121,423 loops=1)

12. 550.150 1,832.953 ↓ 17,346.1 121,423 1

Nested Loop (cost=2,728.41..4,989.09 rows=7 width=16) (actual time=123.279..1,832.953 rows=121,423 loops=1)

13. 129.700 689.656 ↓ 12,894.5 25,789 1

Nested Loop (cost=2,728.00..4,981.96 rows=2 width=16) (actual time=123.249..689.656 rows=25,789 loops=1)

14. 50.248 432.068 ↓ 7,993.0 7,993 1

Nested Loop Left Join (cost=2,727.71..4,981.14 rows=1 width=24) (actual time=123.220..432.068 rows=7,993 loops=1)

15. 48.521 341.855 ↓ 7,993.0 7,993 1

Nested Loop (cost=2,727.42..4,980.36 rows=1 width=20) (actual time=123.200..341.855 rows=7,993 loops=1)

16. 41.482 237.383 ↓ 7,993.0 7,993 1

Hash Join (cost=2,727.14..4,979.96 rows=1 width=16) (actual time=123.146..237.383 rows=7,993 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. 41.612 149.211 ↓ 1.3 9,120 1

Hash Left Join (cost=1,692.61..3,926.62 rows=7,166 width=28) (actual time=76.249..149.211 rows=9,120 loops=1)

  • Hash Cond: (analytical_reports_1.sample_id = samples_last_edition.id)
18. 31.915 33.010 ↓ 1.3 9,120 1

Bitmap Heap Scan on analytical_reports analytical_reports_1 (cost=155.68..2,205.88 rows=7,166 width=20) (actual time=1.282..33.010 rows=9,120 loops=1)

  • Recheck Cond: (NOT preview)
  • Filter: (((sample_id IS NULL) OR (sample_id IS NOT NULL)) AND (tenant_id = 1))
  • Heap Blocks: exact=1171
19. 1.095 1.095 ↑ 1.0 9,120 1

Bitmap Index Scan on index_analytical_reports_on_preview (cost=0.00..153.88 rows=9,216 width=0) (actual time=1.092..1.095 rows=9,120 loops=1)

20. 36.964 74.589 ↓ 1.0 16,399 1

Hash (cost=1,250.97..1,250.97 rows=16,397 width=12) (actual time=74.585..74.589 rows=16,399 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 562kB
21. 37.625 37.625 ↓ 1.0 16,399 1

Seq Scan on samples samples_last_edition (cost=0.00..1,250.97 rows=16,397 width=12) (actual time=0.035..37.625 rows=16,399 loops=1)

22. 19.507 46.690 ↓ 1.0 10,871 1

Hash (cost=898.68..898.68 rows=10,868 width=12) (actual time=46.687..46.690 rows=10,871 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 558kB
23. 27.183 27.183 ↓ 1.0 10,871 1

Seq Scan on work_orders work_orders_last_edition_1 (cost=0.00..898.68 rows=10,868 width=12) (actual time=0.028..27.183 rows=10,871 loops=1)

24. 55.951 55.951 ↑ 1.0 1 7,993

Index Only Scan using work_orders_pkey on work_orders work_order_filter_1 (cost=0.29..0.40 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=7,993)

  • Index Cond: (id = work_orders_last_edition_1.id)
  • Heap Fetches: 3151
25. 39.965 39.965 ↑ 1.0 1 7,993

Index Scan using work_orders_pkey on work_orders t01_1 (cost=0.29..0.78 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=7,993)

  • Index Cond: (id = analytical_reports_1.work_order_id)
26. 127.888 127.888 ↓ 1.5 3 7,993

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.008..0.016 rows=3 loops=7,993)

  • Index Cond: (work_order_id = work_orders_last_edition_1.id)
27. 593.147 593.147 ↓ 1.2 5 25,789

Index Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters sample_parameters_filter_1 (cost=0.41..3.52 rows=4 width=8) (actual time=0.008..0.023 rows=5 loops=25,789)

  • Index Cond: (sample_id = samples_filter_1.id)
28. 485.692 485.692 ↓ 0.0 0 121,423

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.004..0.004 rows=0 loops=121,423)

  • Index Cond: (work_order_id = t01_1.id)
  • Heap Fetches: 342
29. 242.846 242.846 ↓ 0.0 0 121,423

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

  • Index Cond: (id = wocm_1.collecting_method_id)
30. 2,307.037 4,492.651 ↓ 0.0 0 121,423

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

31. 242.846 242.846 ↓ 0.0 0 121,423

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

  • Filter: ((t04_1.translation_key)::text = (key)::text)
32. 242.846 242.846 ↓ 0.0 0 121,423

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.002..0.002 rows=0 loops=121,423)

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

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.002..0.002 rows=0 loops=121,423)

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

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.002..0.002 rows=0 loops=121,423)

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

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.002..0.002 rows=0 loops=121,423)

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

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.002..0.002 rows=0 loops=121,423)

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

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,423)

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

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.002..0.002 rows=0 loops=121,423)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 0
39. 242.846 242.846 ↓ 0.0 0 121,423

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.002..0.002 rows=0 loops=121,423)

  • Index Cond: (key = (t04_1.translation_key)::text)
  • Heap Fetches: 342
40. 3.568 6.989 ↓ 1.0 2,110 1

Hash (cost=49.48..49.48 rows=2,048 width=4) (actual time=6.986..6.989 rows=2,110 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 107kB
41. 3.421 3.421 ↓ 1.0 2,110 1

Seq Scan on analytical_report_publications t07_1 (cost=0.00..49.48 rows=2,048 width=4) (actual time=0.022..3.421 rows=2,110 loops=1)

42. 768.810 768.810 ↑ 1.0 1 128,135

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.006..0.006 rows=1 loops=128,135)

  • Index Cond: ((functional_structure_id = sample_parameters_filter_1.functional_structure_id) AND (user_id = 1))
  • Heap Fetches: 128135
43. 13.276 11,838.962 ↓ 61.2 2,632 1

Nested Loop Left Join (cost=182.98..420.43 rows=43 width=481) (actual time=11,761.990..11,838.962 rows=2,632 loops=1)

44. 12.759 11,812.526 ↓ 61.2 2,632 1

Nested Loop Left Join (cost=182.57..392.75 rows=43 width=469) (actual time=11,761.954..11,812.526 rows=2,632 loops=1)

45. 14.751 11,794.503 ↓ 61.2 2,632 1

Nested Loop Left Join (cost=182.29..369.69 rows=43 width=443) (actual time=11,761.938..11,794.503 rows=2,632 loops=1)

46. 10.328 11,769.224 ↓ 61.2 2,632 1

Hash Right Join (cost=182.01..352.66 rows=43 width=407) (actual time=11,761.891..11,769.224 rows=2,632 loops=1)

  • Hash Cond: ((t05.key)::text = (t04.translation_key)::text)
47. 7.341 11.998 ↑ 1.5 2,374 1

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

48. 0.022 0.022 ↓ 0.0 0 1

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

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

50. 0.010 0.010 ↓ 0.0 0 1

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

51. 0.014 0.014 ↓ 0.0 0 1

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

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

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

54. 0.009 0.009 ↓ 0.0 0 1

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

55. 0.011 0.011 ↓ 0.0 0 1

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

56. 4.563 4.563 ↑ 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.021..4.563 rows=2,374 loops=1)

57. 5.981 11,746.898 ↓ 61.2 2,632 1

Hash (cost=181.47..181.47 rows=43 width=229) (actual time=11,746.897..11,746.898 rows=2,632 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 305kB
58. 8.435 11,740.917 ↓ 61.2 2,632 1

Hash Left Join (cost=124.17..181.47 rows=43 width=229) (actual time=11,727.448..11,740.917 rows=2,632 loops=1)

  • Hash Cond: (wocm.collecting_method_id = t04.id)
59. 8.289 11,731.830 ↓ 61.2 2,632 1

Hash Right Join (cost=116.37..173.56 rows=43 width=152) (actual time=11,726.739..11,731.830 rows=2,632 loops=1)

  • Hash Cond: (t07.analytical_report_id = analytical_reports.id)
60. 4.129 4.129 ↓ 1.0 2,110 1

Seq Scan on analytical_report_publications t07 (cost=0.00..49.48 rows=2,048 width=69) (actual time=0.030..4.129 rows=2,110 loops=1)

61. 5.737 11,719.412 ↓ 61.2 2,632 1

Hash (cost=115.84..115.84 rows=43 width=83) (actual time=11,719.411..11,719.412 rows=2,632 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 304kB
62. 5.350 11,713.675 ↓ 61.2 2,632 1

Hash Right Join (cost=105.94..115.84 rows=43 width=83) (actual time=11,708.193..11,713.675 rows=2,632 loops=1)

  • Hash Cond: (wocm.work_order_id = t01.id)
63. 0.352 0.352 ↑ 1.1 192 1

Seq Scan on work_order_collecting_method wocm (cost=0.00..9.10 rows=210 width=8) (actual time=0.036..0.352 rows=192 loops=1)

64. 6.079 11,707.973 ↓ 61.2 2,632 1

Hash (cost=105.40..105.40 rows=43 width=79) (actual time=11,707.971..11,707.973 rows=2,632 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 304kB
65. 9.691 11,701.894 ↓ 61.2 2,632 1

Nested Loop (cost=1.85..105.40 rows=43 width=79) (actual time=11,673.630..11,701.894 rows=2,632 loops=1)

66. 1.708 11,682.503 ↓ 32.3 388 1

Nested Loop (cost=1.43..79.45 rows=12 width=83) (actual time=11,673.512..11,682.503 rows=388 loops=1)

  • Join Filter: (work_orders_last_edition.id = samples_filter.work_order_id)
67. 0.735 11,678.995 ↓ 12.5 100 1

Nested Loop Left Join (cost=1.14..75.31 rows=8 width=91) (actual time=11,673.465..11,678.995 rows=100 loops=1)

68. 0.559 11,677.760 ↓ 12.5 100 1

Nested Loop (cost=0.86..71.99 rows=8 width=75) (actual time=11,673.442..11,677.760 rows=100 loops=1)

69. 0.605 11,676.801 ↓ 12.5 100 1

Nested Loop (cost=0.57..69.29 rows=8 width=71) (actual time=11,673.423..11,676.801 rows=100 loops=1)

70. 0.561 11,675.596 ↓ 12.5 100 1

Nested Loop (cost=0.29..66.60 rows=8 width=67) (actual time=11,673.380..11,675.596 rows=100 loops=1)

71. 11,674.235 11,674.235 ↓ 12.5 100 1

CTE Scan on q (cost=0.00..0.16 rows=8 width=4) (actual time=11,673.300..11,674.235 rows=100 loops=1)

72. 0.800 0.800 ↑ 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.008..0.008 rows=1 loops=100)

  • Index Cond: (id = q."Id")
73. 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.34 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100)

  • Index Cond: (id = analytical_reports.work_order_id)
  • Heap Fetches: 50
74. 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.34 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (id = analytical_reports.work_order_id)
  • Heap Fetches: 50
75. 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)
76. 1.800 1.800 ↓ 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.018 rows=4 loops=100)

  • Index Cond: (work_order_id = analytical_reports.work_order_id)
77. 9.700 9.700 ↓ 1.8 7 388

Index Only Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters sample_parameters_filter (cost=0.41..2.12 rows=4 width=4) (actual time=0.007..0.025 rows=7 loops=388)

  • Index Cond: (sample_id = samples_filter.id)
  • Heap Fetches: 2124
78. 0.324 0.652 ↑ 1.5 144 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
79. 0.328 0.328 ↑ 1.5 144 1

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

80. 10.528 10.528 ↑ 1.0 1 2,632

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

  • Index Cond: (t01.customer_id = id)
81. 5.264 5.264 ↓ 0.0 0 2,632

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,632)

  • Index Cond: (t01.patient_id = id)
82. 13.160 13.160 ↑ 1.0 1 2,632

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

  • Index Cond: (id = analytical_reports.sample_id)
Planning time : 43.935 ms
Execution time : 11,862.215 ms