explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zcrb : Optimization for: Optimization for: plan #jJl1; plan #ev9W

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.511 7,738.531 ↓ 6.7 100 1

Limit (cost=5,008.00..5,008.04 rows=15 width=8) (actual time=7,737.897..7,738.531 rows=100 loops=1)

2. 16.710 7,738.020 ↓ 6.7 100 1

Sort (cost=5,008.00..5,008.04 rows=15 width=8) (actual time=7,737.891..7,738.020 rows=100 loops=1)

  • Sort Key: analytical_reports.serial DESC
  • Sort Method: top-N heapsort Memory: 34kB
3. 207.632 7,721.310 ↓ 533.1 7,997 1

Group (cost=5,007.63..5,007.71 rows=15 width=8) (actual time=7,309.093..7,721.310 rows=7,997 loops=1)

  • Group Key: analytical_reports.id
4. 501.902 7,513.678 ↓ 8,559.1 128,386 1

Sort (cost=5,007.63..5,007.67 rows=15 width=8) (actual time=7,309.082..7,513.678 rows=128,386 loops=1)

  • Sort Key: analytical_reports.id
  • Sort Method: external merge Disk: 2280kB
5. 684.362 7,011.776 ↓ 8,559.1 128,386 1

Nested Loop (cost=2,730.04..5,007.34 rows=15 width=8) (actual time=117.939..7,011.776 rows=128,386 loops=1)

6. 674.188 5,685.484 ↓ 10,698.8 128,386 1

Nested Loop Left Join (cost=2,729.76..5,003.76 rows=12 width=12) (actual time=117.895..5,685.484 rows=128,386 loops=1)

7. 594.534 4,281.252 ↓ 10,139.5 121,674 1

Nested Loop Left Join (cost=2,729.48..4,999.82 rows=12 width=12) (actual time=117.861..4,281.252 rows=121,674 loops=1)

  • Join Filter: ((t04.translation_key)::text = (t05.key)::text)
8. 556.760 3,565.044 ↓ 10,139.5 121,674 1

Nested Loop Left Join (cost=2,729.48..4,999.63 rows=12 width=54) (actual time=117.832..3,565.044 rows=121,674 loops=1)

9. 573.405 2,764.936 ↓ 10,139.5 121,674 1

Nested Loop Left Join (cost=2,729.34..4,996.46 rows=12 width=16) (actual time=117.819..2,764.936 rows=121,674 loops=1)

10. 515.575 1,704.835 ↓ 10,139.5 121,674 1

Nested Loop (cost=2,729.07..4,992.88 rows=12 width=16) (actual time=117.791..1,704.835 rows=121,674 loops=1)

11. 108.311 646.851 ↓ 12,914.5 25,829 1

Nested Loop (cost=2,728.65..4,985.75 rows=2 width=16) (actual time=117.759..646.851 rows=25,829 loops=1)

12. 42.599 418.540 ↓ 8,000.0 8,000 1

Nested Loop Left Join (cost=2,728.36..4,984.93 rows=1 width=24) (actual time=117.736..418.540 rows=8,000 loops=1)

13. 41.761 335.941 ↓ 8,000.0 8,000 1

Nested Loop (cost=2,728.08..4,984.14 rows=1 width=20) (actual time=117.710..335.941 rows=8,000 loops=1)

14. 42.638 238.180 ↓ 8,000.0 8,000 1

Hash Join (cost=2,727.79..4,983.70 rows=1 width=16) (actual time=117.521..238.180 rows=8,000 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. 48.638 147.230 ↓ 1.3 9,130 1

Hash Left Join (cost=1,692.63..3,929.72 rows=7,170 width=28) (actual time=69.039..147.230 rows=9,130 loops=1)

  • Hash Cond: (analytical_reports.sample_id = samples_last_edition.id)
16. 30.116 31.160 ↓ 1.3 9,130 1

Bitmap Heap Scan on analytical_reports (cost=155.68..2,206.94 rows=7,170 width=20) (actual time=1.236..31.160 rows=9,130 loops=1)

  • Recheck Cond: (NOT preview)
  • Filter: (((sample_id IS NULL) OR (sample_id IS NOT NULL)) AND (tenant_id = 1))
  • Heap Blocks: exact=1173
17. 1.044 1.044 ↑ 1.0 9,138 1

Bitmap Index Scan on index_analytical_reports_on_preview (cost=0.00..153.88 rows=9,221 width=0) (actual time=1.040..1.044 rows=9,138 loops=1)

18. 30.353 67.432 ↑ 1.0 16,397 1

Hash (cost=1,250.98..1,250.98 rows=16,398 width=12) (actual time=67.428..67.432 rows=16,397 loops=1)

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

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

20. 20.414 48.312 ↑ 1.0 10,896 1

Hash (cost=898.96..898.96 rows=10,896 width=12) (actual time=48.309..48.312 rows=10,896 loops=1)

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

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

22. 56.000 56.000 ↑ 1.0 1 8,000

Index Only Scan using work_orders_pkey on work_orders work_order_filter (cost=0.29..0.45 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=8,000)

  • Index Cond: (id = work_orders_last_edition.id)
  • Heap Fetches: 4694
23. 40.000 40.000 ↑ 1.0 1 8,000

Index Scan using work_orders_pkey on work_orders t01 (cost=0.29..0.78 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=8,000)

  • Index Cond: (id = analytical_reports.work_order_id)
24. 120.000 120.000 ↓ 1.5 3 8,000

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.007..0.015 rows=3 loops=8,000)

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

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

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

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

  • Index Cond: (work_order_id = t01.id)
  • Heap Fetches: 249
27. 243.348 243.348 ↓ 0.0 0 121,674

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

  • Index Cond: (id = wocm.collecting_method_id)
28. 121.655 121.674 ↓ 0.0 0 121,674

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

29. 0.005 0.019 ↓ 0.0 0 1

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

30. 0.014 0.014 ↓ 0.0 0 1

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

  • Filter: ((locale_code)::text = 'pt'::text)
31. 730.044 730.044 ↑ 1.0 1 121,674

Index Only Scan using index_arp_on_analytical_report_id on analytical_report_publications t07 (cost=0.28..0.32 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=121,674)

  • Index Cond: (analytical_report_id = analytical_reports.id)
  • Heap Fetches: 66737
32. 641.930 641.930 ↑ 1.0 1 128,386

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

  • Index Cond: ((functional_structure_id = sample_parameters_filter.functional_structure_id) AND (user_id = 1))
  • Heap Fetches: 128386
Planning time : 28.994 ms
Execution time : 7,740.991 ms