explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ev9W : Optimization for: plan #jJl1

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.391 8,453.462 ↓ 6.7 100 1

Limit (cost=5,075.50..5,075.53 rows=15 width=8) (actual time=8,452.940..8,453.462 rows=100 loops=1)

2. 12.602 8,453.071 ↓ 6.7 100 1

Sort (cost=5,075.50..5,075.53 rows=15 width=8) (actual time=8,452.933..8,453.071 rows=100 loops=1)

  • Sort Key: analytical_reports.serial DESC
  • Sort Method: top-N heapsort Memory: 34kB
3. 178.959 8,440.469 ↓ 533.0 7,995 1

Group (cost=5,075.13..5,075.20 rows=15 width=8) (actual time=8,088.986..8,440.469 rows=7,995 loops=1)

  • Group Key: analytical_reports.id
4. 572.854 8,261.510 ↓ 8,551.0 128,265 1

Sort (cost=5,075.13..5,075.17 rows=15 width=8) (actual time=8,088.976..8,261.510 rows=128,265 loops=1)

  • Sort Key: analytical_reports.id
  • Sort Method: external merge Disk: 2272kB
5. 769.701 7,688.656 ↓ 8,551.0 128,265 1

Nested Loop (cost=2,804.21..5,074.83 rows=15 width=8) (actual time=127.599..7,688.656 rows=128,265 loops=1)

6. 397.099 6,149.365 ↓ 10,688.8 128,265 1

Hash Left Join (cost=2,803.93..5,071.25 rows=12 width=12) (actual time=127.551..6,149.365 rows=128,265 loops=1)

  • Hash Cond: (analytical_reports.id = t07.analytical_report_id)
7. 712.378 5,744.978 ↓ 10,129.4 121,553 1

Nested Loop Left Join (cost=2,728.85..4,996.06 rows=12 width=12) (actual time=120.234..5,744.978 rows=121,553 loops=1)

  • Join Filter: ((t04.translation_key)::text = (t05.key)::text)
8. 662.552 4,911.047 ↓ 10,129.4 121,553 1

Nested Loop Left Join (cost=2,728.85..4,995.87 rows=12 width=54) (actual time=120.187..4,911.047 rows=121,553 loops=1)

9. 643.522 4,005.389 ↓ 10,129.4 121,553 1

Nested Loop Left Join (cost=2,728.71..4,992.70 rows=12 width=16) (actual time=120.173..4,005.389 rows=121,553 loops=1)

10. 785.412 2,754.102 ↓ 10,129.4 121,553 1

Nested Loop (cost=2,728.44..4,989.11 rows=12 width=16) (actual time=120.145..2,754.102 rows=121,553 loops=1)

11. 144.693 1,116.894 ↓ 12,906.0 25,812 1

Nested Loop (cost=2,728.02..4,981.98 rows=2 width=16) (actual time=120.108..1,116.894 rows=25,812 loops=1)

12. 74.985 668.277 ↓ 7,998.0 7,998 1

Nested Loop Left Join (cost=2,727.73..4,981.16 rows=1 width=24) (actual time=120.085..668.277 rows=7,998 loops=1)

13. 84.472 497.316 ↓ 7,998.0 7,998 1

Nested Loop (cost=2,727.45..4,980.38 rows=1 width=20) (actual time=120.067..497.316 rows=7,998 loops=1)

14. 53.002 324.866 ↓ 7,998.0 7,998 1

Hash Join (cost=2,727.16..4,979.98 rows=1 width=16) (actual time=120.014..324.866 rows=7,998 loops=1)

  • Hash Cond: (analytical_reports.work_order_id = work_orders_last_edition.id)
  • Join Filter: (((analytical_reports.sample_id IS NULL) AND (analytical_reports.serial = work_orders_last_edition.report_serial) AND (analytical_reports.edition = work_orders_last_edition.report_edition)) OR ((analytical_reports.sample_id IS NOT NULL) AND (analytical_reports.serial = samples_last_edition.report_serial) AND (analytical_reports.edition = samples_last_edition.report_edition)))
  • Rows Removed by Join Filter: 1130
15. 77.807 224.116 ↓ 1.3 9,128 1

Hash Left Join (cost=1,692.63..3,926.64 rows=7,166 width=28) (actual time=72.094..224.116 rows=9,128 loops=1)

  • Hash Cond: (analytical_reports.sample_id = samples_last_edition.id)
16. 74.764 75.843 ↓ 1.3 9,128 1

Bitmap Heap Scan on analytical_reports (cost=155.68..2,205.88 rows=7,166 width=20) (actual time=1.276..75.843 rows=9,128 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
17. 1.079 1.079 ↑ 1.0 9,130 1

Bitmap Index Scan on index_analytical_reports_on_preview (cost=0.00..153.88 rows=9,216 width=0) (actual time=1.076..1.079 rows=9,130 loops=1)

18. 33.319 70.466 ↑ 1.0 16,398 1

Hash (cost=1,250.98..1,250.98 rows=16,398 width=12) (actual time=70.463..70.466 rows=16,398 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 561kB
19. 37.147 37.147 ↑ 1.0 16,398 1

Seq Scan on samples samples_last_edition (cost=0.00..1,250.98 rows=16,398 width=12) (actual time=0.038..37.147 rows=16,398 loops=1)

20. 19.738 47.748 ↓ 1.0 10,896 1

Hash (cost=898.68..898.68 rows=10,868 width=12) (actual time=47.745..47.748 rows=10,896 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 559kB
21. 28.010 28.010 ↓ 1.0 10,896 1

Seq Scan on work_orders work_orders_last_edition (cost=0.00..898.68 rows=10,868 width=12) (actual time=0.023..28.010 rows=10,896 loops=1)

22. 87.978 87.978 ↑ 1.0 1 7,998

Index Only Scan using work_orders_pkey on work_orders work_order_filter (cost=0.29..0.40 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=7,998)

  • Index Cond: (id = work_orders_last_edition.id)
  • Heap Fetches: 4532
23. 95.976 95.976 ↑ 1.0 1 7,998

Index Scan using work_orders_pkey on work_orders t01 (cost=0.29..0.78 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=7,998)

  • Index Cond: (id = analytical_reports.work_order_id)
24. 303.924 303.924 ↓ 1.5 3 7,998

Index Scan using index_samples_on_work_order_id_state on samples samples_filter (cost=0.29..0.80 rows=2 width=8) (actual time=0.021..0.038 rows=3 loops=7,998)

  • Index Cond: (work_order_id = work_orders_last_edition.id)
25. 851.796 851.796 ↓ 1.2 5 25,812

Index Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters sample_parameters_filter (cost=0.42..3.53 rows=4 width=8) (actual time=0.009..0.033 rows=5 loops=25,812)

  • Index Cond: (sample_id = samples_filter.id)
26. 607.765 607.765 ↓ 0.0 0 121,553

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

  • Index Cond: (work_order_id = t01.id)
  • Heap Fetches: 183
27. 243.106 243.106 ↓ 0.0 0 121,553

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

  • Index Cond: (id = wocm.collecting_method_id)
28. 121.526 121.553 ↓ 0.0 0 121,553

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

29. 0.005 0.027 ↓ 0.0 0 1

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

30. 0.022 0.022 ↓ 0.0 0 1

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

  • Filter: ((locale_code)::text = 'pt'::text)
31. 3.755 7.288 ↓ 1.0 2,118 1

Hash (cost=49.48..49.48 rows=2,048 width=4) (actual time=7.285..7.288 rows=2,118 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 107kB
32. 3.533 3.533 ↓ 1.0 2,118 1

Seq Scan on analytical_report_publications t07 (cost=0.00..49.48 rows=2,048 width=4) (actual time=0.020..3.533 rows=2,118 loops=1)

33. 769.590 769.590 ↑ 1.0 1 128,265

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

  • Index Cond: ((functional_structure_id = sample_parameters_filter.functional_structure_id) AND (user_id = 1))
  • Heap Fetches: 128265
Planning time : 28.134 ms
Execution time : 8,455.960 ms