explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fWRt : Optimization for: plan #Dup

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=240,361.86..240,381.33 rows=147 width=439) (actual rows= loops=)

  • Group Key: ("substring"((fil.denominazione)::text, 16, 10)), tri.denominazione, cli.cognome, cli.nome, ("substring"((cli.subjectstatestring)::text, 25, 15)), cli.ndg, motb.id_mo, motb.stato_contratto, motb.data_inserimento, motb.codice_attivita, clp.label, (max(kbtc2.data_inserimento)), (max(kbtc3.data_inserimento)), mor.id_mo, motcento.data_intervento, motcento.numero, (CASE WHEN (count(*) > 0) THEN 'S'::text ELSE 'N'::text END), (CASE WHEN (count(*) > 0) THEN 'S'::text ELSE 'N'::text END), (min(kbtc1.data_inserimento)), (CASE WHEN ((min(kbtc1.data_inserimento)) IS NULL) THEN 'OK'::text ELSE CASE WHEN (vischi1.data_controllo IS NULL) THEN CASE WHEN ((('now'::cstring)::date - '40 days'::interval) < (min(kbtc1.data_inserimento))) THEN 'OK'::text ELSE 'KO'::text END ELSE CASE WHEN ((vischi1.data_controllo - '40 days'::interval) < (min(kbtc1.data_inserimento))) THEN 'OK'::text ELSE 'KO'::text END END END), vischi1.data_controllo, vischi2.data_controllo, vischi3.data_controllo, motb.note, (CASE WHEN (motcento.sconto_autotrapianto = 0) THEN 'Nessuno'::text WHEN (motcento.sconto_autotrapianto = 1) THEN 'SANDERS 250'::text WHEN (motcento.sconto_autotrapianto = 2) THEN 'PARRUCCHIERI 250'::text WHEN (motcento.sconto_autotrapianto = 3) THEN 'SANDERS 500'::text ELSE NULL::text END), motcento.codice_attivita, kbtp2.serum, cli.statosoggettotrapianto
2. 0.000 0.000 ↓ 0.0

Sort (cost=240,361.86..240,362.22 rows=147 width=439) (actual rows= loops=)

  • Sort Key: ("substring"((fil.denominazione)::text, 16, 10)), tri.denominazione, cli.cognome, cli.nome, ("substring"((cli.subjectstatestring)::text, 25, 15)), cli.ndg, motb.id_mo, motb.stato_contratto, motb.data_inserimento, motb.codice_attivita, clp.label, (max(kbtc2.data_inserimento)), (max(kbtc3.data_inserimento)), mor.id_mo, motcento.data_intervento, motcento.numero, (CASE WHEN (count(*) > 0) THEN 'S'::text ELSE 'N'::text END), (CASE WHEN (count(*) > 0) THEN 'S'::text ELSE 'N'::text END), (min(kbtc1.data_inserimento)), (CASE WHEN ((min(kbtc1.data_inserimento)) IS NULL) THEN 'OK'::text ELSE CASE WHEN (vischi1.data_controllo IS NULL) THEN CASE WHEN ((('now'::cstring)::date - '40 days'::interval) < (min(kbtc1.data_inserimento))) THEN 'OK'::text ELSE 'KO'::text END ELSE CASE WHEN ((vischi1.data_controllo - '40 days'::interval) < (min(kbtc1.data_inserimento))) THEN 'OK'::text ELSE 'KO'::text END END END), vischi1.data_controllo, vischi2.data_controllo, vischi3.data_controllo, motb.note, (CASE WHEN (motcento.sconto_autotrapianto = 0) THEN 'Nessuno'::text WHEN (motcento.sconto_autotrapianto = 1) THEN 'SANDERS 250'::text WHEN (motcento.sconto_autotrapianto = 2) THEN 'PARRUCCHIERI 250'::text WHEN (motcento.sconto_autotrapianto = 3) THEN 'SANDERS 500'::text ELSE NULL::text END), motcento.codice_attivita, kbtp2.serum, cli.statosoggettotrapianto
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=238,023.30..240,356.57 rows=147 width=439) (actual rows= loops=)

  • Hash Cond: (cli.id = vischi3.cliente)
4. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=231,825.98..234,154.27 rows=147 width=435) (actual rows= loops=)

  • Merge Cond: (cli.id = vischi2.cliente)
5. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=225,626.67..227,954.10 rows=147 width=427) (actual rows= loops=)

  • Merge Cond: (cli.id = vischi1.cliente)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=219,427.36..221,753.93 rows=147 width=419) (actual rows= loops=)

  • Join Filter: (motcento.cliente = cli.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=191,463.44..192,391.89 rows=1 width=337) (actual rows= loops=)

  • Join Filter: (mott_1.cliente = cli.id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=187,749.94..188,460.48 rows=1 width=305) (actual rows= loops=)

  • Join Filter: (mott.cliente = cli.id)
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=184,045.27..184,567.12 rows=1 width=273) (actual rows= loops=)

  • Join Filter: (kbtc3.cliente = cli.id)
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=133,476.77..133,750.16 rows=1 width=265) (actual rows= loops=)

  • Join Filter: (kbtc2.cliente = cli.id)
11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=82,688.84..82,688.87 rows=1 width=257) (actual rows= loops=)

  • Merge Cond: (cli.id = kbtp2.cliente)
12. 0.000 0.000 ↓ 0.0

Sort (cost=53,199.22..53,199.23 rows=1 width=225) (actual rows= loops=)

  • Sort Key: cli.id
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=52,403.03..53,199.21 rows=1 width=225) (actual rows= loops=)

  • Join Filter: (kbtc1.cliente = cli.id)
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12,240.16..13,004.89 rows=1 width=217) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12,239.87..13,004.46 rows=1 width=167) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12,239.45..13,003.89 rows=1 width=148) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12,239.03..13,003.32 rows=1 width=129) (actual rows= loops=)

  • Join Filter: (mota.cliente = cli.id)
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,238.61..13,002.55 rows=1 width=57) (actual rows= loops=)

  • Hash Cond: ((motc.cliente = mota.cliente) AND ((min(motc.id)) = motb.id))
19. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,941.51..4,219.31 rows=27,779 width=8) (actual rows= loops=)

  • Group Key: motc.cliente
20. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata motc (cost=0.00..3,647.64 rows=58,775 width=8) (actual rows= loops=)

  • Filter: (((stato_contratto)::text <> 'AN'::text) AND ((codice_attivita)::text <> '100'::text))
21. 0.000 0.000 ↓ 0.0

Hash (cost=8,086.42..8,086.42 rows=14,045 width=57) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,378.30..8,086.42 rows=14,045 width=57) (actual rows= loops=)

  • Hash Cond: (motb.cliente = mota.cliente)
23. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata motb (cost=0.00..3,327.76 rows=63,976 width=53) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=4,295.33..4,295.33 rows=6,638 width=4) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,162.57..4,228.95 rows=6,638 width=4) (actual rows= loops=)

  • Group Key: mota.cliente
26. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata mota (cost=0.00..4,127.46 rows=14,043 width=4) (actual rows= loops=)

  • Filter: (((stato_contratto)::text <> 'AN'::text) AND (((codice_attivita)::text = '100'::text) OR ((codice_attivita)::text = '205'::text) OR ((codice_attivita)::text = '110'::text) OR ((codice_attivita)::text = '111'::text)))
27. 0.000 0.000 ↓ 0.0

Index Scan using banksubject_pkey on banksubject cli (cost=0.42..0.76 rows=1 width=72) (actual rows= loops=)

  • Index Cond: (id = motb.cliente)
28. 0.000 0.000 ↓ 0.0

Index Scan using banksubject_pkey on banksubject fil (cost=0.42..0.56 rows=1 width=27) (actual rows= loops=)

  • Index Cond: (id = cli.filiale_ass)
29. 0.000 0.000 ↓ 0.0

Index Scan using banksubject_pkey on banksubject tri (cost=0.42..0.56 rows=1 width=27) (actual rows= loops=)

  • Index Cond: (id = cli.profes_ass)
30. 0.000 0.000 ↓ 0.0

Index Scan using c_lookup_pkey on c_lookup clp (cost=0.29..0.41 rows=1 width=58) (actual rows= loops=)

  • Index Cond: (id = motb.mo_type)
31. 0.000 0.000 ↓ 0.0

HashAggregate (cost=40,162.86..40,172.54 rows=968 width=12) (actual rows= loops=)

  • Group Key: kbtc1.cliente
32. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on kb_testata kbtc1 (cost=1,049.69..39,885.92 rows=55,389 width=12) (actual rows= loops=)

  • Recheck Cond: (consegna IS NOT NULL)
33. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on kb_testata_consegna_key (cost=0.00..1,035.85 rows=55,389 width=0) (actual rows= loops=)

  • Index Cond: (consegna IS NOT NULL)
34. 0.000 0.000 ↓ 0.0

Sort (cost=29,489.62..29,489.63 rows=3 width=36) (actual rows= loops=)

  • Sort Key: kbtp2.cliente
35. 0.000 0.000 ↓ 0.0

Subquery Scan on kbtp2 (cost=29,489.53..29,489.59 rows=3 width=36) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

HashAggregate (cost=29,489.53..29,489.56 rows=3 width=12) (actual rows= loops=)

  • Group Key: kbtc4.cliente
37. 0.000 0.000 ↓ 0.0

Append (cost=28,492.86..29,489.51 rows=3 width=12) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

HashAggregate (cost=28,492.86..28,492.87 rows=1 width=8) (actual rows= loops=)

  • Group Key: kbtc4.cliente
39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..28,492.86 rows=1 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on kb_prodotti_igienici_dettaglio pid (cost=0.00..28,484.40 rows=1 width=8) (actual rows= loops=)

  • Filter: prod_tricopigmentazione
41. 0.000 0.000 ↓ 0.0

Index Scan using "idx_kt_prodotto igenico" on kb_testata kbtc4 (cost=0.43..8.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (prodotto_igienico = pid.id_testata)
42. 0.000 0.000 ↓ 0.0

HashAggregate (cost=382.93..382.94 rows=1 width=8) (actual rows= loops=)

  • Group Key: kbtc4_1.cliente
43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..382.93 rows=1 width=8) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on kb_pagamento_contrassegno_prodotti_igienici_dettaglio pid_1 (cost=0.00..374.47 rows=1 width=8) (actual rows= loops=)

  • Filter: prod_tricopigmentazione
45. 0.000 0.000 ↓ 0.0

Index Scan using kb_testata_pagamento_contrassegno_prodotto_igienico_key on kb_testata kbtc4_1 (cost=0.43..8.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (pagamento_contrassegno_prodotto_igienico = pid_1.id_testata)
46. 0.000 0.000 ↓ 0.0

HashAggregate (cost=613.66..613.67 rows=1 width=8) (actual rows= loops=)

  • Group Key: kbtc4_2.cliente
47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..613.66 rows=1 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on kb_nota_credito_dettaglio pid_2 (cost=0.00..605.20 rows=1 width=8) (actual rows= loops=)

  • Filter: prod_tricopigmentazione
49. 0.000 0.000 ↓ 0.0

Index Scan using kb_testata_nota_credito_key on kb_testata kbtc4_2 (cost=0.43..8.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (nota_credito = pid_2.id_testata)
50. 0.000 0.000 ↓ 0.0

HashAggregate (cost=50,787.93..50,872.04 rows=8,411 width=12) (actual rows= loops=)

  • Group Key: kbtc2.cliente
51. 0.000 0.000 ↓ 0.0

Seq Scan on kb_testata kbtc2 (cost=0.00..48,380.50 rows=481,486 width=12) (actual rows= loops=)

  • Filter: (visita IS NOT NULL)
52. 0.000 0.000 ↓ 0.0

HashAggregate (cost=50,568.50..50,644.95 rows=7,645 width=12) (actual rows= loops=)

  • Group Key: kbtc3.cliente
53. 0.000 0.000 ↓ 0.0

Seq Scan on kb_testata kbtc3 (cost=0.00..48,380.50 rows=437,600 width=12) (actual rows= loops=)

  • Filter: (prodotto_igienico IS NOT NULL)
54. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,704.67..3,772.06 rows=5,391 width=4) (actual rows= loops=)

  • Group Key: mott.cliente
55. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata mott (cost=0.00..3,647.64 rows=11,406 width=4) (actual rows= loops=)

  • Filter: ((codice_attivita)::text = ANY ('{006,060,603,610}'::text[]))
56. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,713.50..3,791.33 rows=6,226 width=4) (actual rows= loops=)

  • Group Key: mott_1.cliente
57. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata mott_1 (cost=0.00..3,647.64 rows=13,173 width=4) (actual rows= loops=)

  • Filter: ((codice_attivita)::text = ANY ('{003,030,303,305}'::text[]))
58. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=27,963.91..29,360.20 rows=147 width=98) (actual rows= loops=)

  • Merge Cond: (mor.modulo_ordine = motcento.primo)
59. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=19,600.02..20,627.24 rows=29,349 width=23) (actual rows= loops=)

  • Group Key: mor.modulo_ordine, mor.id_mo, mor.type_rata
60. 0.000 0.000 ↓ 0.0

Sort (cost=19,600.02..19,673.40 rows=29,349 width=23) (actual rows= loops=)

  • Sort Key: mor.modulo_ordine, mor.id_mo, mor.type_rata
61. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,027.03..17,422.18 rows=29,349 width=23) (actual rows= loops=)

  • Hash Cond: (mor.modulo_ordine = mort.id)
62. 0.000 0.000 ↓ 0.0

Seq Scan on mo_rata mor (cost=0.00..11,622.75 rows=394,375 width=23) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=3,967.52..3,967.52 rows=4,761 width=4) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata mort (cost=0.00..3,967.52 rows=4,761 width=4) (actual rows= loops=)

  • Filter: (((stato_contratto)::text <> 'AN'::text) AND (((codice_attivita)::text = '100'::text) OR ((codice_attivita)::text = '110'::text) OR ((codice_attivita)::text = '111'::text)))
65. 0.000 0.000 ↓ 0.0

Materialize (cost=8,363.89..8,363.89 rows=1 width=27) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=8,363.89..8,363.89 rows=1 width=27) (actual rows= loops=)

  • Sort Key: motcento.primo
67. 0.000 0.000 ↓ 0.0

Subquery Scan on motcento (cost=8,363.86..8,363.88 rows=1 width=27) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

HashAggregate (cost=8,363.86..8,363.87 rows=1 width=38) (actual rows= loops=)

  • Group Key: mot.cliente, mot.data_intervento, (count(*)), mot.sconto_autotrapianto, mot.codice_attivita, mot.id_mo
69. 0.000 0.000 ↓ 0.0

Hash Join (cost=8,290.67..8,363.84 rows=1 width=38) (actual rows= loops=)

  • Hash Cond: ((mo_testata.cliente = mot.cliente) AND ((mo_testata.codice_attivita)::text = (mot.codice_attivita)::text))
70. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,003.23..4,025.74 rows=2,251 width=7) (actual rows= loops=)

  • Group Key: mo_testata.cliente, mo_testata.codice_attivita
71. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata (cost=0.00..3,967.52 rows=4,761 width=7) (actual rows= loops=)

  • Filter: (((stato_contratto)::text <> 'AN'::text) AND (((codice_attivita)::text = '100'::text) OR ((codice_attivita)::text = '110'::text) OR ((codice_attivita)::text = '111'::text)))
72. 0.000 0.000 ↓ 0.0

Hash (cost=4,287.40..4,287.40 rows=3 width=30) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Seq Scan on mo_testata mot (cost=0.00..4,287.40 rows=3 width=30) (actual rows= loops=)

  • Filter: (((stato_contratto)::text <> 'AN'::text) AND (data_intervento >= '2018-08-01'::date) AND (data_intervento <= '2018-08-30'::date) AND (((codice_attivita)::text = '100'::text) OR ((codice_attivita)::text = '110'::text) OR ((codice_attivita)::text = '111'::text)))
74. 0.000 0.000 ↓ 0.0

Sort (cost=6,199.31..6,199.55 rows=97 width=12) (actual rows= loops=)

  • Sort Key: vischi1.cliente
75. 0.000 0.000 ↓ 0.0

Subquery Scan on vischi1 (cost=6,194.17..6,196.11 rows=97 width=12) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,194.17..6,195.14 rows=97 width=19) (actual rows= loops=)

  • Group Key: age.soggetto, age.prestazione_fk
77. 0.000 0.000 ↓ 0.0

Index Scan using idx_ap_preestazione_fk on age_prenotazione age (cost=0.43..6,186.91 rows=968 width=19) (actual rows= loops=)

  • Index Cond: ((prestazione_fk)::text = 'CONSULT01'::text)
  • Filter: ((stato)::text = ANY ('{C,P,VIS}'::text[]))
78. 0.000 0.000 ↓ 0.0

Sort (cost=6,199.31..6,199.55 rows=97 width=12) (actual rows= loops=)

  • Sort Key: vischi2.cliente
79. 0.000 0.000 ↓ 0.0

Subquery Scan on vischi2 (cost=6,194.17..6,196.11 rows=97 width=12) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,194.17..6,195.14 rows=97 width=19) (actual rows= loops=)

  • Group Key: age_1.soggetto, age_1.prestazione_fk
81. 0.000 0.000 ↓ 0.0

Index Scan using idx_ap_preestazione_fk on age_prenotazione age_1 (cost=0.43..6,186.91 rows=968 width=19) (actual rows= loops=)

  • Index Cond: ((prestazione_fk)::text = 'CONTR6MESI'::text)
  • Filter: ((stato)::text = ANY ('{C,P,VIS}'::text[]))
82. 0.000 0.000 ↓ 0.0

Hash (cost=6,196.11..6,196.11 rows=97 width=12) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Subquery Scan on vischi3 (cost=6,194.17..6,196.11 rows=97 width=12) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,194.17..6,195.14 rows=97 width=19) (actual rows= loops=)

  • Group Key: age_2.soggetto, age_2.prestazione_fk
85. 0.000 0.000 ↓ 0.0

Index Scan using idx_ap_preestazione_fk on age_prenotazione age_2 (cost=0.43..6,186.91 rows=968 width=19) (actual rows= loops=)

  • Index Cond: ((prestazione_fk)::text = 'CONTR12MESI01'::text)