explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f2MM

Settings
# exclusive inclusive rows x rows loops node
1. 62.442 418,315.536 ↓ 1.4 2,322 1

Nested Loop Left Join (cost=1,248.56..13,685,761.58 rows=1,615 width=1,076) (actual time=3,100.271..418,315.536 rows=2,322 loops=1)

  • Join Filter: (lto.code = v.tipologia_operatore)
  • Rows Removed by Join Filter: 81,270
  • Buffers: shared hit=249,733 read=30,523, temp read=8,756,133 written=4,581
2. 216,718.219 418,241.484 ↓ 2.2 2,322 1

Nested Loop Left Join (cost=1,248.56..13,680,870.11 rows=1,042 width=802) (actual time=3,100.058..418,241.484 rows=2,322 loops=1)

  • Join Filter: (((v.riferimento_id = t.org_id) AND (v.riferimento_id_nome_col = 'org_id'::text)) OR ((v.riferimento_id = t.id_stabilimento) AND (v.riferimento_id_nome_col = 'id_stabilimento'::text)) OR ((v.riferimento_id = t.alt_id) AND (v.riferimento_id_nome_col = 'alt_id'::text)))
  • Rows Removed by Join Filter: 1,033,060,735
  • Buffers: shared hit=249,733 read=30,522, temp read=8,756,133 written=4,581
3. 12.687 42,309.186 ↓ 2.2 2,287 1

Hash Left Join (cost=1,248.56..480,040.67 rows=1,042 width=773) (actual time=2,385.830..42,309.186 rows=2,287 loops=1)

  • Hash Cond: (t.site_id = asl.code)
  • Buffers: shared hit=206,132 read=30,522, temp read=753 written=752
4. 27,832.657 42,296.461 ↓ 2.2 2,287 1

Nested Loop (cost=1,247.02..480,035.91 rows=1,042 width=257) (actual time=2,385.699..42,296.461 rows=2,287 loops=1)

  • Join Filter: ((p.org_id = t.org_id) OR (p.stab_id = t.id_stabilimento) OR (p.alt_id = t.alt_id))
  • Rows aRemoved by Join Filter: 181,597,353
  • Buffers: shared hit=206,128 read=30,522, temp read=753 written=752
5. 459.086 459.086 ↓ 231.8 76,949 1

Function Scan on dblink p (cost=0.00..15.00 rows=332 width=84) (actual time=381.550..459.086 rows=76,949 loops=1)

  • Filter: ((id > 0) AND (header <> ''::text))
  • Rows Removed by Filter: 1,708
  • Buffers: shared read=2, temp read=753 written=752
6. 12,692.426 14,004.718 ↑ 10.9 2,360 76,949

Materialize (cost=1,247.02..83,269.49 rows=25,814 width=185) (actual time=0.006..0.182 rows=2,360 loops=76,949)

  • Buffers: shared hit=206,128 read=30,520
7. 6.937 1,312.292 ↑ 10.9 2,360 1

Nested Loop (cost=1,247.02..82,459.42 rows=25,814 width=185) (actual time=484.946..1,312.292 rows=2,360 loops=1)

  • Buffers: shared hit=206,128 read=30,520
8. 0.000 1,269.580 ↑ 18.0 2,385 1

Gather (cost=1,246.59..57,108.56 rows=42,907 width=157) (actual time=484.878..1,269.580 rows=2,385 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=196,951 read=30,149
9. 118.331 1,286.606 ↑ 21.2 1,192 2 / 2

Hash Join (cost=246.59..51,817.86 rows=25,239 width=157) (actual time=481.118..1,286.606 rows=1,192 loops=2)

  • Hash Cond: ((tcu.pianomonitoraggio)::text = (lpiani.code)::text)
  • Buffers: shared hit=196,951 read=30,149
10. 1,160.770 1,160.770 ↑ 1.1 489,052 2 / 2

Parallel Index Scan using tipocontrolloufficialeimprese_tipoispezione_idx on tipocontrolloufficialeimprese tcu (cost=0.43..46,411.68 rows=560,872 width=8) (actual time=11.161..1,160.770 rows=489,052 loops=2)

  • Index Cond: (tipoispezione > 0)
  • Filter: (enabled AND (id_lookup_condizionalita < 0))
  • Rows Removed by Filter: 19,024
  • Buffers: shared hit=196,709 read=29,959
11. 0.034 7.505 ↑ 1.0 9 2 / 2

Hash (cost=246.05..246.05 rows=9 width=157) (actual time=7.505..7.505 rows=9 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=192 read=190
12. 7.471 7.471 ↑ 1.0 9 2 / 2

Seq Scan on lookup_piano_monitoraggio lpiani (cost=0.00..246.05 rows=9 width=157) (actual time=1.032..7.471 rows=9 loops=2)

  • Filter: (codice_interno = 1,345)
  • Rows Removed by Filter: 4,395
  • Buffers: shared hit=192 read=190
13. 35.775 35.775 ↑ 1.0 1 2,385

Index Scan using ticket_pkey on ticket t (cost=0.43..0.59 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=2,385)

  • Index Cond: (ticketid = tcu.idcontrollo)
  • Filter: ((trashed_date IS NULL) AND (assigned_date >= '1900-01-01 00:00:00'::timestamp without time zone) AND (tipologia = 3) AND (assigned_date <= (now())::timestamp without time zone))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=9,177 read=371
14. 0.012 0.038 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=520) (actual time=0.038..0.038 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
15. 0.026 0.026 ↑ 1.0 24 1

Seq Scan on lookup_site_id asl (cost=0.00..1.24 rows=24 width=520) (actual time=0.023..0.026 rows=24 loops=1)

  • Buffers: shared hit=1
16. 158,540.612 159,214.079 ↓ 1.3 451,711 2,287

Materialize (cost=0.00..56,505.94 rows=338,600 width=56) (actual time=0.007..69.617 rows=451,711 loops=2,287)

  • Buffers: shared hit=43,601, temp read=8,755,380 written=3,829
17. 673.467 673.467 ↓ 1.3 451,711 1

Seq Scan on ricerche_anagrafiche_old_materializzata v (cost=0.00..51,505.94 rows=338,600 width=56) (actual time=0.014..673.467 rows=451,711 loops=1)

  • Filter: ((riferimento_id_nome_col = 'org_id'::text) OR (riferimento_id_nome_col = 'id_stabilimento'::text) OR (riferimento_id_nome_col = 'alt_id'::text))
  • Buffers: shared hit=43,601
18. 11.569 11.610 ↑ 8.6 36 2,322

Materialize (cost=0.00..14.65 rows=310 width=222) (actual time=0.001..0.005 rows=36 loops=2,322)

  • Buffers: shared read=1
19. 0.041 0.041 ↑ 8.6 36 1

Seq Scan on lookup_tipologia_operatore lto (cost=0.00..13.10 rows=310 width=222) (actual time=0.032..0.041 rows=36 loops=1)

  • Buffers: shared read=1
Planning time : 22.608 ms
Execution time : 418,335.364 ms