explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jJl1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11.223 6,269.177 ↓ 61.2 2,633 1

Sort (cost=5,321.26..5,321.37 rows=43 width=851) (actual time=6,266.110..6,269.177 rows=2,633 loops=1)

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

CTE q

3. 0.336 6,131.600 ↓ 12.5 100 1

Limit (cost=5,070.64..5,070.66 rows=8 width=8) (actual time=6,131.086..6,131.600 rows=100 loops=1)

4. 11.944 6,131.264 ↓ 12.5 100 1

Sort (cost=5,070.64..5,070.66 rows=8 width=8) (actual time=6,131.080..6,131.264 rows=100 loops=1)

  • Sort Key: analytical_reports_1.serial DESC
  • Sort Method: top-N heapsort Memory: 34kB
5. 168.243 6,119.320 ↓ 998.9 7,991 1

Group (cost=5,070.48..5,070.52 rows=8 width=8) (actual time=5,789.502..6,119.320 rows=7,991 loops=1)

  • Group Key: analytical_reports_1.id
6. 441.914 5,951.077 ↓ 16,016.9 128,135 1

Sort (cost=5,070.48..5,070.50 rows=8 width=8) (actual time=5,789.491..5,951.077 rows=128,135 loops=1)

  • Sort Key: analytical_reports_1.id
  • Sort Method: external merge Disk: 2272kB
7. 562.258 5,509.163 ↓ 16,016.9 128,135 1

Nested Loop (cost=2,804.18..5,070.36 rows=8 width=8) (actual time=123.725..5,509.163 rows=128,135 loops=1)

8. 379.250 4,306.230 ↓ 18,305.0 128,135 1

Hash Left Join (cost=2,803.91..5,068.27 rows=7 width=12) (actual time=123.608..4,306.230 rows=128,135 loops=1)

  • Hash Cond: (analytical_reports_1.id = t07_1.analytical_report_id)
9. 546.112 3,919.599 ↓ 17,346.1 121,423 1

Nested Loop Left Join (cost=2,728.83..4,993.12 rows=7 width=12) (actual time=116.161..3,919.599 rows=121,423 loops=1)

  • Join Filter: ((t04_1.translation_key)::text = (t05_1.key)::text)
10. 575.986 3,252.064 ↓ 17,346.1 121,423 1

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

11. 511.323 2,554.655 ↓ 17,346.1 121,423 1

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

12. 467.429 1,557.640 ↓ 17,346.1 121,423 1

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

13. 95.910 600.201 ↓ 12,895.0 25,790 1

Nested Loop (cost=2,728.00..4,981.96 rows=2 width=16) (actual time=116.050..600.201 rows=25,790 loops=1)

14. 41.369 392.375 ↓ 7,994.0 7,994 1

Nested Loop Left Join (cost=2,727.71..4,981.14 rows=1 width=24) (actual time=116.026..392.375 rows=7,994 loops=1)

15. 44.403 311.036 ↓ 7,994.0 7,994 1

Nested Loop (cost=2,727.42..4,980.36 rows=1 width=20) (actual time=116.005..311.036 rows=7,994 loops=1)

16. 37.194 218.669 ↓ 7,994.0 7,994 1

Hash Join (cost=2,727.14..4,979.96 rows=1 width=16) (actual time=115.823..218.669 rows=7,994 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: 1129
17. 37.387 133.759 ↓ 1.3 9,123 1

Hash Left Join (cost=1,692.61..3,926.62 rows=7,166 width=28) (actual time=67.903..133.759 rows=9,123 loops=1)

  • Hash Cond: (analytical_reports_1.sample_id = samples_last_edition.id)
18. 29.099 30.121 ↓ 1.3 9,123 1

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

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

Bitmap Index Scan on index_analytical_reports_on_preview (cost=0.00..153.88 rows=9,216 width=0) (actual time=1.019..1.022 rows=9,125 loops=1)

20. 29.882 66.251 ↓ 1.0 16,398 1

Hash (cost=1,250.97..1,250.97 rows=16,397 width=12) (actual time=66.247..66.251 rows=16,398 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 561kB
21. 36.369 36.369 ↓ 1.0 16,398 1

Seq Scan on samples samples_last_edition (cost=0.00..1,250.97 rows=16,397 width=12) (actual time=0.037..36.369 rows=16,398 loops=1)

22. 19.659 47.716 ↓ 1.0 10,889 1

Hash (cost=898.68..898.68 rows=10,868 width=12) (actual time=47.713..47.716 rows=10,889 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 559kB
23. 28.057 28.057 ↓ 1.0 10,889 1

Seq Scan on work_orders work_orders_last_edition_1 (cost=0.00..898.68 rows=10,868 width=12) (actual time=0.023..28.057 rows=10,889 loops=1)

24. 47.964 47.964 ↑ 1.0 1 7,994

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

  • Index Cond: (id = work_orders_last_edition_1.id)
  • Heap Fetches: 3714
25. 39.970 39.970 ↑ 1.0 1 7,994

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

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

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.007..0.014 rows=3 loops=7,994)

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

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.007..0.019 rows=5 loops=25,790)

  • 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. 121.423 121.423 ↓ 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.001..0.001 rows=0 loops=121,423)

  • Index Cond: (id = wocm_1.collecting_method_id)
30. 121.404 121.423 ↓ 0.0 0 121,423

Materialize (cost=0.00..0.01 rows=1 width=516) (actual time=0.001..0.001 rows=0 loops=121,423)

31. 0.005 0.019 ↓ 0.0 0 1

Append (cost=0.00..0.01 rows=1 width=516) (actual time=0.017..0.019 rows=0 loops=1)

32. 0.014 0.014 ↓ 0.0 0 1

Seq Scan on translations t05_1 (cost=0.00..0.00 rows=1 width=516) (actual time=0.012..0.014 rows=0 loops=1)

  • Filter: ((locale_code)::text = 'pt'::text)
33. 3.722 7.381 ↓ 1.0 2,117 1

Hash (cost=49.48..49.48 rows=2,048 width=4) (actual time=7.378..7.381 rows=2,117 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 107kB
34. 3.659 3.659 ↓ 1.0 2,117 1

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

35. 640.675 640.675 ↑ 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.005..0.005 rows=1 loops=128,135)

  • Index Cond: ((functional_structure_id = sample_parameters_filter_1.functional_structure_id) AND (user_id = 1))
  • Heap Fetches: 128135
36. 11.117 6,257.954 ↓ 61.2 2,633 1

Nested Loop Left Join (cost=125.16..249.43 rows=43 width=851) (actual time=6,176.619..6,257.954 rows=2,633 loops=1)

37. 7.214 6,233.672 ↓ 61.2 2,633 1

Hash Left Join (cost=124.75..221.75 rows=43 width=839) (actual time=6,176.585..6,233.672 rows=2,633 loops=1)

  • Hash Cond: ((t04.translation_key)::text = (t05.key)::text)
38. 7.507 6,226.439 ↓ 61.2 2,633 1

Hash Left Join (cost=124.73..221.56 rows=43 width=291) (actual time=6,176.473..6,226.439 rows=2,633 loops=1)

  • Hash Cond: (wocm.collecting_method_id = t04.id)
39. 9.985 6,218.392 ↓ 61.2 2,633 1

Nested Loop Left Join (cost=116.94..213.66 rows=43 width=214) (actual time=6,175.898..6,218.392 rows=2,633 loops=1)

40. 12.011 6,203.141 ↓ 61.2 2,633 1

Nested Loop Left Join (cost=116.66..190.60 rows=43 width=188) (actual time=6,175.742..6,203.141 rows=2,633 loops=1)

41. 7.569 6,180.598 ↓ 61.2 2,633 1

Hash Right Join (cost=116.38..173.57 rows=43 width=152) (actual time=6,175.704..6,180.598 rows=2,633 loops=1)

  • Hash Cond: (t07.analytical_report_id = analytical_reports.id)
42. 2.723 2.723 ↓ 1.0 2,117 1

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

43. 4.929 6,170.306 ↓ 61.2 2,633 1

Hash (cost=115.84..115.84 rows=43 width=83) (actual time=6,170.305..6,170.306 rows=2,633 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 304kB
44. 4.465 6,165.377 ↓ 61.2 2,633 1

Hash Right Join (cost=105.94..115.84 rows=43 width=83) (actual time=6,160.692..6,165.377 rows=2,633 loops=1)

  • Hash Cond: (wocm.work_order_id = t01.id)
45. 0.289 0.289 ↑ 1.1 194 1

Seq Scan on work_order_collecting_method wocm (cost=0.00..9.10 rows=210 width=8) (actual time=0.030..0.289 rows=194 loops=1)

46. 5.322 6,160.623 ↓ 61.2 2,633 1

Hash (cost=105.40..105.40 rows=43 width=79) (actual time=6,160.621..6,160.623 rows=2,633 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 304kB
47. 7.674 6,155.301 ↓ 61.2 2,633 1

Nested Loop (cost=1.85..105.40 rows=43 width=79) (actual time=6,131.489..6,155.301 rows=2,633 loops=1)

48. 1.396 6,139.069 ↓ 32.4 389 1

Nested Loop (cost=1.43..79.46 rows=12 width=83) (actual time=6,131.314..6,139.069 rows=389 loops=1)

  • Join Filter: (work_orders_last_edition.id = samples_filter.work_order_id)
49. 0.558 6,136.073 ↓ 12.5 100 1

Nested Loop Left Join (cost=1.14..75.32 rows=8 width=91) (actual time=6,131.274..6,136.073 rows=100 loops=1)

50. 0.466 6,135.015 ↓ 12.5 100 1

Nested Loop (cost=0.86..71.99 rows=8 width=75) (actual time=6,131.250..6,135.015 rows=100 loops=1)

51. 0.488 6,134.149 ↓ 12.5 100 1

Nested Loop (cost=0.57..69.30 rows=8 width=71) (actual time=6,131.204..6,134.149 rows=100 loops=1)

52. 0.480 6,133.061 ↓ 12.5 100 1

Nested Loop (cost=0.29..66.60 rows=8 width=67) (actual time=6,131.163..6,133.061 rows=100 loops=1)

53. 6,131.881 6,131.881 ↓ 12.5 100 1

CTE Scan on q (cost=0.00..0.16 rows=8 width=4) (actual time=6,131.096..6,131.881 rows=100 loops=1)

54. 0.700 0.700 ↑ 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.007..0.007 rows=1 loops=100)

  • Index Cond: (id = q."Id")
55. 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: 58
56. 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: 58
57. 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)
58. 1.600 1.600 ↓ 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.007..0.016 rows=4 loops=100)

  • Index Cond: (work_order_id = analytical_reports.work_order_id)
59. 8.558 8.558 ↓ 1.8 7 389

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.022 rows=7 loops=389)

  • Index Cond: (sample_id = samples_filter.id)
  • Heap Fetches: 2192
60. 10.532 10.532 ↑ 1.0 1 2,633

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

  • Index Cond: (t01.customer_id = id)
61. 5.266 5.266 ↓ 0.0 0 2,633

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

  • Index Cond: (t01.patient_id = id)
62. 0.291 0.540 ↑ 1.5 144 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
63. 0.249 0.249 ↑ 1.5 144 1

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

64. 0.003 0.019 ↓ 0.0 0 1

Hash (cost=0.01..0.01 rows=1 width=548) (actual time=0.018..0.019 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
65. 0.005 0.016 ↓ 0.0 0 1

Append (cost=0.00..0.01 rows=1 width=548) (actual time=0.015..0.016 rows=0 loops=1)

66. 0.011 0.011 ↓ 0.0 0 1

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

  • Filter: ((locale_code)::text = 'pt'::text)
67. 13.165 13.165 ↑ 1.0 1 2,633

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

  • Index Cond: (id = analytical_reports.sample_id)
Planning time : 47.970 ms
Execution time : 6,277.166 ms