explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nt3E

Settings
# exclusive inclusive rows x rows loops node
1. 0.910 1,789,104.614 ↓ 3.5 378 1

Unique (cost=641,295.15..641,304.25 rows=107 width=1,368) (actual time=1,789,103.629..1,789,104.614 rows=378 loops=1)

2. 3.192 1,789,103.704 ↓ 3.5 378 1

Sort (cost=641,295.15..641,295.42 rows=107 width=1,368) (actual time=1,789,103.626..1,789,103.704 rows=378 loops=1)

  • Sort Key: ticket.id_bdn, ticket.id_bdn_b11, o.name, o.org_id, ticket.ticketid, ticket.assigned_date, (CASE WHEN (ticket.site_id = 201) THEN 'R201'::text WHEN (ticket.site_id = 202) THEN 'R103'::text WHEN (ticket.site_id = 203) THEN 'R203'::text WHEN (ticket.site_id = 204) THEN 'R106'::text WHEN (ticket.site_id = 205) THEN 'R205'::text WHEN (ticket.site_id = 206) THEN 'R206'::text WHEN (ticket.site_id = 207) THEN 'R207'::text ELSE NULL::text END), asl1.description, o.account_number, o.partita_iva, (CASE WHEN (((nucleo.nucleo_ispettivo)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_due)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_tre)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_quattro)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_cinque)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_sei)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_sette)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_otto)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_nove)::text ~~* '%forestale% '::text) OR ((nucleo.nucleo_ispettivo_dieci)::text ~~* '%forestale% '::text)) THEN '002'::text WHEN (((nucleo.nucleo_ispettivo)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_due)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_tre)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_quattro)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_cinque)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_sei)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_sette)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_otto)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_nove)::text ~~* '%NAS% '::text) OR ((nucleo.nucleo_ispettivo_dieci)::text ~~* '%NAS% '::text)) THEN '001'::text WHEN (((nucleo.nucleo_ispettivo)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_due)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_tre)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_quattro)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_cinque)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_sei)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_sette)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_otto)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_nove)::text ~~* '%veterinari% '::text) OR ((nucleo.nucleo_ispettivo_dieci)::text ~~* '%veterinari% '::text)) THEN '003'::text ELSE '003'::text END), (CASE WHEN (ticket.closed IS NULL) THEN 'APERTO'::text WHEN ticket.closed_nolista THEN 'CHIUSO SENZA LISTA PER ASSENZA DI NON CONFORMITA'''::text ELSE 'CHIUSO'::text END), o.cf_detentore, op.tipo_produzione, (CASE WHEN (op.codice_orientamento IS NOT NULL) THEN op.codice_orientamento ELSE ''::text END), ticket.data_estrazione, ticket.data_import, ticket.esito_import, ticket.descrizione_errore, ticket.data_estrazione_b11, ticket.data_import_b11, ticket.esito_import_b11, ticket.descrizione_errore_b11, (CASE WHEN ((ticket.data_estrazione_b11 IS NULL) OR (ticket.data_import_b11 IS NULL) OR ((ticket.data_import_b11 IS NOT NULL) AND (ticket.esito_import_b11 = 'KO'::text))) THEN 'I'::text WHEN ((ticket.modified > ticket.data_estrazione_b11) OR (ticket.data_import_b11 < ticket.data_estrazione_b11)) THEN 'V'::text ELSE NULL::text END), o.specie_allev, (('0'::text || (o.specie_allevamento)::text)), (CASE WHEN ((((o.specie_allev)::text ~~* 'gallus'::text) OR ((o.specie_allev)::text ~~* 'oche'::text) OR ((o.specie_allev)::text ~~* 'fagiani'::text)) AND (o.codice_tipo_allevamento IS NOT NULL)) THEN o.codice_tipo_allevamento ELSE '4'::text END), (CASE WHEN ((ticket.flag_preavviso IS NULL) OR (ticket.flag_preavviso ~~* '-1'::text)) THEN 'N'::text ELSE ticket.flag_preavviso END), ticket.data_preavviso_ba, (CASE WHEN (ist.mod_b11_flag_rilascio_copia OR ((ticket.flag_checklist)::text = 'S'::text)) THEN 'S'::text ELSE 'N'::text END), (CASE WHEN ((SubPlan 1) = 0) THEN 'S'::text ELSE 'N'::text END), lcm.code, lpiani.code
  • Sort Method: quicksort Memory: 194kB
3. 18.605 1,789,100.512 ↓ 3.5 378 1

Hash Left Join (cost=205,308.69..641,291.54 rows=107 width=1,368) (actual time=197,260.864..1,789,100.512 rows=378 loops=1)

  • Hash Cond: ((o.specie_allev)::text = (specie.description)::text)
4. 2.538 1,788,951.440 ↓ 3.5 378 1

Hash Left Join (cost=205,306.93..640,712.63 rows=107 width=1,198) (actual time=197,260.239..1,788,951.440 rows=378 loops=1)

  • Hash Cond: ((o.orientamento_prod = op.descrizione_codice_orientamento) AND ((o.specie_allev)::text = op.specie_allevata) AND (o.tipologia_strutt = op.descrizione_tipo_produzione))
5. 61.848 1,788,948.278 ↓ 3.5 378 1

Hash Join (cost=205,292.81..640,688.06 rows=107 width=1,213) (actual time=197,259.539..1,788,948.278 rows=378 loops=1)

  • Hash Cond: (ticket.ticketid = tipi.idcontrollo)
6. 190.712 1,788,880.988 ↑ 1.8 77,591 1

Hash Left Join (cost=197,921.72..632,790.27 rows=140,168 width=1,209) (actual time=10,667.532..1,788,880.988 rows=77,591 loops=1)

  • Hash Cond: (ticket.site_id = asl1.code)
7. 1,616,667.383 1,788,690.219 ↑ 1.8 77,591 1

Nested Loop Left Join (cost=197,920.18..632,356.49 rows=140,168 width=693) (actual time=10,667.423..1,788,690.219 rows=77,591 loops=1)

  • Join Filter: ((comuni1.nome)::text ~~* (oa.city)::text)
  • Rows Removed by Join Filter: 628,021,711
8. 213.386 17,151.200 ↓ 22.4 77,591 1

Nested Loop Left Join (cost=197,920.18..211,593.53 rows=3,463 width=704) (actual time=10,665.808..17,151.200 rows=77,591 loops=1)

9. 289.571 15,230.812 ↓ 22.4 77,591 1

Nested Loop Left Join (cost=197,919.76..209,227.12 rows=3,463 width=552) (actual time=10,665.743..15,230.812 rows=77,591 loops=1)

10. 1,573.066 11,527.237 ↓ 22.4 77,591 1

Hash Right Join (cost=197,919.33..202,572.36 rows=3,463 width=541) (actual time=10,665.670..11,527.237 rows=77,591 loops=1)

  • Hash Cond: (risp.idmodist = ist.id)
  • Join Filter: (lcm.codice_specie = o.specie_allevamento)
  • Rows Removed by Join Filter: 15,962
11. 45.773 45.773 ↑ 1.0 76,544 1

Seq Scan on chk_bns_risposte risp (cost=0.00..3,558.44 rows=76,544 width=4) (actual time=0.016..45.773 rows=76,544 loops=1)

12. 498.293 9,908.398 ↓ 22.4 77,591 1

Hash (cost=197,876.04..197,876.04 rows=3,463 width=549) (actual time=9,908.398..9,908.398 rows=77,591 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 512 (originally 1) Memory Usage: 15,317kB
13. 423.119 9,410.105 ↓ 22.4 77,591 1

Nested Loop (cost=126.13..197,876.04 rows=3,463 width=549) (actual time=3.890..9,410.105 rows=77,591 loops=1)

14. 265.400 7,572.066 ↓ 10.2 282,984 1

Hash Left Join (cost=125.71..174,820.76 rows=27,758 width=445) (actual time=3.802..7,572.066 rows=282,984 loops=1)

  • Hash Cond: (ist.id_alleg = lcm.code)
  • Filter: ((lcm.code IS NULL) OR (lcm.code = ANY ('{20,22,23}'::integer[])))
  • Rows Removed by Filter: 1,016
15. 339.094 7,306.628 ↓ 1.1 284,000 1

Hash Left Join (cost=124.06..174,012.09 rows=257,183 width=441) (actual time=3.747..7,306.628 rows=284,000 loops=1)

  • Hash Cond: (ticket.ticketid = ist.idcu)
16. 6,963.889 6,963.889 ↓ 1.1 283,788 1

Seq Scan on ticket (cost=0.00..164,561.13 rows=257,183 width=432) (actual time=0.083..6,963.889 rows=283,788 loops=1)

  • Filter: ((trashed_date IS NULL) AND (tipologia = 3) AND (assigned_date > ((((date_part('year'::text, now()) - '2'::double precision))::text || '-01-01'::text))::timestamp without time zone))
  • Rows Removed by Filter: 1,228,656
17. 1.183 3.645 ↑ 1.0 2,402 1

Hash (cost=94.03..94.03 rows=2,402 width=13) (actual time=3.645..3.645 rows=2,402 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 138kB
18. 2.462 2.462 ↑ 1.0 2,402 1

Seq Scan on chk_bns_mod_ist ist (cost=0.00..94.03 rows=2,402 width=13) (actual time=0.016..2.462 rows=2,402 loops=1)

  • Filter: (trashed_date IS NULL)
  • Rows Removed by Filter: 1,701
19. 0.013 0.038 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=8) (actual time=0.038..0.038 rows=29 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.025 0.025 ↑ 1.0 29 1

Seq Scan on lookup_chk_bns_mod lcm (cost=0.00..1.29 rows=29 width=8) (actual time=0.012..0.025 rows=29 loops=1)

21. 1,414.920 1,414.920 ↓ 0.0 0 282,984

Index Scan using organization_org_id_idx on organization o (cost=0.42..0.83 rows=1 width=112) (actual time=0.005..0.005 rows=0 loops=282,984)

  • Index Cond: (org_id = ticket.org_id)
  • Filter: ((trashed_date IS NULL) AND (tipologia = 2))
  • Rows Removed by Filter: 0
22. 3,414.004 3,414.004 ↑ 1.0 1 77,591

Index Scan using organization_address_org_id_idx on organization_address oa (cost=0.42..1.91 rows=1 width=15) (actual time=0.043..0.044 rows=1 loops=77,591)

  • Index Cond: (org_id = o.org_id)
  • Filter: (address_type = 1)
  • Rows Removed by Filter: 0
23. 1,707.002 1,707.002 ↑ 1.0 1 77,591

Index Scan using nucleo_ispettivo_mv_id_controllo_ufficiale_idx on nucleo_ispettivo_mv nucleo (cost=0.42..0.67 rows=1 width=156) (actual time=0.020..0.022 rows=1 loops=77,591)

  • Index Cond: (id_controllo_ufficiale = ticket.ticketid)
24. 154,865.836 154,871.636 ↑ 1.0 8,095 77,591

Materialize (cost=0.00..288.43 rows=8,095 width=12) (actual time=0.000..1.996 rows=8,095 loops=77,591)

25. 5.800 5.800 ↑ 1.0 8,095 1

Seq Scan on comuni1 (cost=0.00..247.95 rows=8,095 width=12) (actual time=0.015..5.800 rows=8,095 loops=1)

26. 0.023 0.057 ↑ 1.0 24 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
27. 0.034 0.034 ↑ 1.0 24 1

Seq Scan on lookup_site_id asl1 (cost=0.00..1.24 rows=24 width=520) (actual time=0.025..0.034 rows=24 loops=1)

28. 0.357 5.442 ↑ 1.6 750 1

Hash (cost=7,356.45..7,356.45 rows=1,171 width=8) (actual time=5.442..5.442 rows=750 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 46kB
29. 0.449 5.085 ↑ 1.6 750 1

Nested Loop (cost=0.43..7,356.45 rows=1,171 width=8) (actual time=0.541..5.085 rows=750 loops=1)

30. 2.684 2.684 ↓ 4.0 8 1

Seq Scan on lookup_piano_monitoraggio lpiani (cost=0.00..234.28 rows=2 width=4) (actual time=0.448..2.684 rows=8 loops=1)

  • Filter: (codice_interno = 1,483)
  • Rows Removed by Filter: 4,254
31. 1.952 1.952 ↑ 24.6 94 8

Index Scan using tipocontrolloufficialeimprese_pianomonitoraggio_idx on tipocontrolloufficialeimprese tipi (cost=0.43..3,537.95 rows=2,314 width=8) (actual time=0.021..0.244 rows=94 loops=8)

  • Index Cond: (pianomonitoraggio = lpiani.code)
  • Filter: enabled
  • Rows Removed by Filter: 27
32. 0.378 0.624 ↑ 1.0 368 1

Hash (cost=7.68..7.68 rows=368 width=50) (actual time=0.624..0.624 rows=368 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
33. 0.246 0.246 ↑ 1.0 368 1

Seq Scan on orientamenti_produttivi op (cost=0.00..7.68 rows=368 width=50) (actual time=0.033..0.246 rows=368 loops=1)

34. 0.034 0.057 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=516) (actual time=0.057..0.057 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.023 0.023 ↑ 1.0 34 1

Seq Scan on lookup_specie_allevata specie (cost=0.00..1.34 rows=34 width=516) (actual time=0.011..0.023 rows=34 loops=1)

36.          

SubPlan (for Hash Left Join)

37. 130.032 130.410 ↑ 1,000.0 1 378

ProjectSet (cost=0.00..5.27 rows=1,000 width=4) (actual time=0.344..0.345 rows=1 loops=378)

38. 0.378 0.378 ↑ 1.0 1 378

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=378)

Planning time : 13.604 ms
Execution time : 1,789,105.650 ms