Re: [postgresql-it] Passaggio da 9.3 a 10 e calo prestazioni
- From: Nicola Nardi <nicola.nardi@xxxxxxxx>
- To: postgresql-it <postgresql-it@xxxxxxxxxxxxx>
- Date: Wed, 26 Sep 2018 16:44:01 +0200
Intanto vi do le informazioni che ho...
Se le query le mando più volte ci mettono lo stesso tempo.
L'ambiente con il PG 10 è un ambiente di test dove vado solo io.
Non so se anche il resto sia lento in quanto ci siamo fermati direttamente
su questo che il nodo cruciale.
Ho gli Explain analyze dei due ambienti, che vi allego. Dandoci un occhio
vedo che sul 9.3 fa degli HASH mentre sul 10 delle MERGE... oltre al fatto
che sul PG 10 ci ha messo più di un ora!
Ho lanciato la EXPLAIN(analyze, buffers) ma ci vorrà un po'.
Questi test li stiamo facendo da qualche giorno, quindi NON è un problema
momentaneo.
I parametri RDS sono quelli di default su entrambi gli ambienti.
Grazie.
Ciao
Nicola
Il giorno mer 26 set 2018 alle ore 13:47 Fabio Pardi <f.pardi@xxxxxxxxxxxx>
ha scritto:
On 26/09/18 12:37, Enrico Pirozzi wrote:
Ciao Nicola,
potresti gentilmente inviare un explain analyze testuale classico?
+1
i tuoi allegati son 3000 linee di testo, un po troppe per essere lette e
confrontate.
fai cosi:
EXPLAIN(analyze, buffers) SELECT ...
inoltre manda come richiesto la configurazione di ambedue i PostgreSQL e
le risposte mancanti alla mia precedente mail.
saluti,
fabio pardi
_______________________________________________
Postgresql-it mailing list
Postgresql-it@xxxxxxxxxxxxx
http://lists.psql.it/mailman/listinfo/postgresql-it
PG 9.3 (7 minuti) Explain analyze
"GroupAggregate (cost=250644.48..250658.69 rows=116 width=438) (actual
time=454170.955..454171.040 rows=30 loops=1)"
" -> Sort (cost=250644.48..250644.77 rows=116 width=438) (actual
time=454170.934..454170.943 rows=58 loops=1)"
" 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, (sum((sum(pid.quantita)))),
cli.statosoggettotrapianto"
" Sort Method: quicksort Memory: 45kB"
" -> Hash Left Join (cost=240309.44..250640.50 rows=116 width=438)
(actual time=31855.550..454169.673 rows=58 loops=1)"
" Hash Cond: (cli.id = vischi3.cliente)"
" -> Hash Left Join (cost=234585.78..244912.91 rows=116
width=434) (actual time=31844.396..454158.095 rows=58 loops=1)"
" Hash Cond: (cli.id = vischi2.cliente)"
" -> Hash Left Join (cost=228862.12..239188.81 rows=116
width=426) (actual time=31824.480..454138.056 rows=58 loops=1)"
" Hash Cond: (cli.id = vischi1.cliente)"
" -> Nested Loop (cost=223138.46..233464.71 rows=116
width=418) (actual time=31761.953..454075.351 rows=58 loops=1)"
" Join Filter: (motcento.cliente = cli.id)"
" Rows Removed by Join Filter: 494102"
" -> Nested Loop Left Join
(cost=197718.88..207058.01 rows=1 width=336) (actual time=2291.781..388433.550
rows=8520 loops=1)"
" Join Filter: (mott_1.cliente = cli.id)"
" Rows Removed by Join Filter: 49440130"
" -> Nested Loop Left Join
(cost=194539.46..203675.98 rows=1 width=304) (actual time=2250.675..359071.307
rows=8520 loops=1)"
" Join Filter: (mott.cliente =
cli.id)"
" Rows Removed by Join Filter:
84269085"
" -> Nested Loop Left Join
(cost=191369.47..200335.51 rows=1 width=272) (actual time=2205.893..308906.722
rows=8520 loops=1)"
" Join Filter: (kbtc4.cliente
= cli.id)"
" Rows Removed by Join Filter:
93709"
" -> Nested Loop Left Join
(cost=166968.90..175934.84 rows=1 width=240) (actual time=1259.718..307852.930
rows=8520 loops=1)"
" Join Filter:
(kbtc3.cliente = cli.id)"
" Rows Removed by Join
Filter: 212328885"
" -> Nested Loop Left
Join (cost=110913.68..119655.79 rows=1 width=232) (actual
time=821.832..190174.266 rows=8520 loops=1)"
" Join Filter:
(kbtc2.cliente = cli.id)"
" Rows Removed by
Join Filter: 200128456"
" -> Nested Loop
Left Join (cost=54716.24..63217.85 rows=1 width=224) (actual
time=340.810..81438.257 rows=8520 loops=1)"
" Join
Filter: (kbtc1.cliente = cli.id)"
" Rows
Removed by Join Filter: 148301292"
" -> Nested
Loop (cost=7468.48..15943.64 rows=1 width=216) (actual time=113.588..947.971
rows=8520 loops=1)"
" ->
Nested Loop (cost=7468.19..15943.20 rows=1 width=166) (actual
time=113.574..810.265 rows=8520 loops=1)"
"
-> Nested Loop (cost=7467.77..15942.62 rows=1 width=148) (actual
time=113.555..718.733 rows=8578 loops=1)"
"
-> Nested Loop (cost=7467.35..15942.05 rows=1 width=130) (actual
time=113.543..631.749 rows=8581 loops=1)"
"
Join Filter: (mota.cliente = cli.id)"
"
-> Hash Join (cost=7466.93..15941.26 rows=1 width=58) (actual
time=113.471..420.618 rows=8581 loops=1)"
"
Hash Cond: ((motd.cliente = mota.cliente) AND (motd.id =
motb.id))"
"
-> Subquery Scan on motd (cost=0.29..7973.64 rows=24796
width=8) (actual time=0.031..207.342 rows=30408 loops=1)"
"
-> GroupAggregate (cost=0.29..7725.68 rows=24796
width=8) (actual time=0.031..192.307 rows=30408 loops=1)"
"
-> Index Scan using idx_mt_cliente on
mo_testata motc (cost=0.29..7222.82 rows=50979 width=8) (actual
time=0.015..153.338 rows=51020 loops=1)"
"
Filter: (((stato_contratto)::text <>
'AN'::text) AND ((codice_attivita)::text <> '100'::text))"
"
Rows Removed by Filter: 4125"
"
-> Hash (cost=7177.45..7177.45 rows=11213 width=58)
(actual time=112.765..112.765 rows=17280 loops=1)"
"
Buckets: 2048 Batches: 2 Memory Usage: 776kB"
"
-> Hash Join (cost=3738.67..7177.45 rows=11213
width=58) (actual time=53.083..103.539 rows=17280 loops=1)"
"
Hash Cond: (motb.cliente = mota.cliente)"
"
-> Seq Scan on mo_testata motb
(cost=0.00..2844.28 rows=55128 width=54) (actual time=0.016..13.055 rows=55145
loops=1)"
"
-> Hash (cost=3670.49..3670.49 rows=5454
width=4) (actual time=53.026..53.026 rows=8618 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage:
303kB"
"
-> HashAggregate (cost=3561.41..3615.95
rows=5454 width=4) (actual time=48.270..50.615 rows=8618 loops=1)"
"
-> Seq Scan on mo_testata mota
(cost=0.00..3533.38 rows=11213 width=4) (actual time=0.021..43.788 rows=11460
loops=1)"
"
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)))"
"
Rows Removed by Filter: 43685"
"
-> Index Scan using banksubject_pkey on banksubject cli
(cost=0.42..0.77 rows=1 width=72) (actual time=0.017..0.019 rows=1 loops=8581)"
"
Index Cond: (id = motb.cliente)"
"
-> Index Scan using banksubject_pkey on banksubject fil
(cost=0.42..0.56 rows=1 width=26) (actual time=0.006..0.007 rows=1 loops=8581)"
"
Index Cond: (id = cli.filiale_ass)"
"
-> Index Scan using banksubject_pkey on banksubject tri (cost=0.42..0.56
rows=1 width=26) (actual time=0.005..0.007 rows=1 loops=8578)"
"
Index Cond: (id = cli.profes_ass)"
" ->
Index Scan using c_lookup_pkey on c_lookup clp (cost=0.29..0.43 rows=1
width=58) (actual time=0.007..0.013 rows=1 loops=8520)"
"
Index Cond: (id = motb.mo_type)"
" ->
HashAggregate (cost=47247.76..47255.90 rows=814 width=12) (actual
time=0.027..5.964 rows=17407 loops=8520)"
" ->
Bitmap Heap Scan on kb_testata kbtc1 (cost=1014.09..47022.17 rows=45118
width=12) (actual time=55.661..194.965 rows=45241 loops=1)"
"
Recheck Cond: (consegna IS NOT NULL)"
"
Rows Removed by Index Recheck: 203146"
"
-> Bitmap Index Scan on kb_testata_consegna_key (cost=0.00..1002.81
rows=45118 width=0) (actual time=53.224..53.224 rows=45286 loops=1)"
"
Index Cond: (consegna IS NOT NULL)"
" ->
HashAggregate (cost=56197.43..56271.43 rows=7400 width=12) (actual
time=0.057..8.252 rows=23490 loops=8520)"
" -> Seq
Scan on kb_testata kbtc2 (cost=0.00..54144.94 rows=410499 width=12) (actual
time=0.200..315.173 rows=410320 loops=1)"
"
Filter: (visita IS NOT NULL)"
" Rows
Removed by Filter: 612737"
" -> HashAggregate
(cost=56055.23..56124.10 rows=6887 width=12) (actual time=0.053..8.913
rows=24922 loops=8520)"
" -> Seq Scan on
kb_testata kbtc3 (cost=0.00..54144.94 rows=382057 width=12) (actual
time=0.017..288.031 rows=383637 loops=1)"
" Filter:
(prodotto_igienico IS NOT NULL)"
" Rows
Removed by Filter: 639420"
" -> HashAggregate
(cost=24400.57..24400.60 rows=3 width=12) (actual time=0.113..0.117 rows=11
loops=8520)"
" -> Append
(cost=23408.24..24400.55 rows=3 width=12) (actual time=939.736..946.117 rows=11
loops=1)"
" ->
HashAggregate (cost=23408.24..23408.25 rows=1 width=8) (actual
time=939.736..939.739 rows=11 loops=1)"
" -> Nested
Loop (cost=0.42..23408.23 rows=1 width=8) (actual time=667.041..939.688
rows=11 loops=1)"
" ->
Seq Scan on kb_prodotti_igienici_dettaglio pid (cost=0.00..23399.78 rows=1
width=8) (actual time=663.550..923.646 rows=11 loops=1)"
"
Filter: prod_tricopigmentazione"
"
Rows Removed by Filter: 796353"
" ->
Index Scan using "idx_kt_prodotto igenico" on kb_testata kbtc4
(cost=0.42..8.45 rows=1 width=8) (actual time=1.451..1.452 rows=1 loops=11)"
"
Index Cond: (prodotto_igienico = pid.id_testata)"
" ->
HashAggregate (cost=352.28..352.29 rows=1 width=8) (actual time=2.976..2.976
rows=0 loops=1)"
" -> Nested
Loop (cost=0.42..352.27 rows=1 width=8) (actual time=2.974..2.974 rows=0
loops=1)"
" ->
Seq Scan on kb_pagamento_contrassegno_prodotti_igienici_dettaglio pid_1
(cost=0.00..343.82 rows=1 width=8) (actual time=2.974..2.974 rows=0 loops=1)"
"
Filter: prod_tricopigmentazione"
"
Rows Removed by Filter: 10288"
" ->
Index Scan using kb_testata_pagamento_contrassegno_prodotto_igienico_key on
kb_testata kbtc4_1 (cost=0.42..8.45 rows=1 width=8) (never executed)"
"
Index Cond: (pagamento_contrassegno_prodotto_igienico = pid_1.id_testata)"
" ->
HashAggregate (cost=639.97..639.98 rows=1 width=8) (actual time=3.397..3.397
rows=0 loops=1)"
" -> Nested
Loop (cost=0.42..639.96 rows=1 width=8) (actual time=3.395..3.395 rows=0
loops=1)"
" ->
Seq Scan on kb_nota_credito_dettaglio pid_2 (cost=0.00..631.51 rows=1 width=8)
(actual time=3.394..3.394 rows=0 loops=1)"
"
Filter: prod_tricopigmentazione"
"
Rows Removed by Filter: 9551"
" ->
Index Scan using kb_testata_nota_credito_key on kb_testata kbtc4_2
(cost=0.42..8.45 rows=1 width=8) (never executed)"
"
Index Cond: (nota_credito = pid_2.id_testata)"
" -> HashAggregate
(cost=3169.98..3230.87 rows=4871 width=4) (actual time=0.007..3.967 rows=9891
loops=8520)"
" -> Seq Scan on mo_testata
mott (cost=0.00..3119.92 rows=10013 width=4) (actual time=0.017..32.215
rows=9970 loops=1)"
" Filter:
((codice_attivita)::text = ANY ('{006,060,603,610}'::text[]))"
" Rows Removed by
Filter: 45175"
" -> HashAggregate
(cost=3179.42..3251.78 rows=5789 width=4) (actual time=0.006..2.314 rows=5803
loops=8520)"
" -> Seq Scan on mo_testata mott_1
(cost=0.00..3119.92 rows=11900 width=4) (actual time=0.014..32.098 rows=11561
loops=1)"
" Filter:
((codice_attivita)::text = ANY ('{003,030,303,305}'::text[]))"
" Rows Removed by Filter:
43584"
" -> Merge Right Join (cost=25419.58..26405.25
rows=116 width=98) (actual time=6.707..7.685 rows=58 loops=8520)"
" Merge Cond: (mor.modulo_ordine =
motcento.primo)"
" -> GroupAggregate
(cost=18222.29..18916.82 rows=23151 width=23) (actual time=0.034..6.359
rows=6605 loops=8520)"
" -> Sort (cost=18222.29..18280.17
rows=23151 width=23) (actual time=0.028..1.581 rows=6613 loops=8520)"
" Sort Key: mor.modulo_ordine,
mor.id_mo, mor.type_rata"
" Sort Method: quicksort
Memory: 721kB"
" -> Hash Join
(cost=3443.16..16067.99 rows=23151 width=23) (actual time=34.081..215.241
rows=6771 loops=1)"
" Hash Cond:
(mor.modulo_ordine = mort.id)"
" -> Seq Scan on
mo_rata mor (cost=0.00..9879.61 rows=335161 width=23) (actual
time=0.004..69.780 rows=335281 loops=1)"
" -> Hash
(cost=3395.56..3395.56 rows=3808 width=4) (actual time=33.990..33.990 rows=3576
loops=1)"
" Buckets: 1024
Batches: 1 Memory Usage: 126kB"
" -> Seq Scan on
mo_testata mort (cost=0.00..3395.56 rows=3808 width=4) (actual
time=0.031..32.768 rows=3576 loops=1)"
" Filter:
(((stato_contratto)::text <> 'AN'::text) AND (((codice_attivita)::text =
'100'::text) OR ((codice_attivita)::text = '110'::text) OR
((codice_attivita)::text = '111'::text)))"
" Rows
Removed by Filter: 51569"
" -> Materialize (cost=7197.29..7197.30
rows=1 width=27) (actual time=0.012..0.024 rows=57 loops=8520)"
" -> Sort (cost=7197.29..7197.30
rows=1 width=27) (actual time=85.923..85.930 rows=30 loops=1)"
" Sort Key: motcento.primo"
" Sort Method: quicksort
Memory: 27kB"
" -> Subquery Scan on
motcento (cost=7197.26..7197.28 rows=1 width=27) (actual time=85.875..85.901
rows=30 loops=1)"
" -> HashAggregate
(cost=7197.26..7197.27 rows=1 width=38) (actual time=85.873..85.890 rows=30
loops=1)"
" -> Nested Loop
(cost=3424.12..7197.24 rows=1 width=38) (actual time=47.147..85.848 rows=30
loops=1)"
" Join
Filter: ((mot.cliente = mo_testata.cliente) AND ((mot.codice_attivita)::text =
(mo_testata.codice_attivita)::text))"
" Rows
Removed by Join Filter: 97470"
" ->
HashAggregate (cost=3424.12..3442.65 rows=1853 width=7) (actual
time=35.807..37.407 rows=3250 loops=1)"
" ->
Seq Scan on mo_testata (cost=0.00..3395.56 rows=3808 width=7) (actual
time=0.033..33.234 rows=3576 loops=1)"
"
Filter: (((stato_contratto)::text <> 'AN'::text) AND (((codice_attivita)::text
= '100'::text) OR ((codice_attivita)::text = '110'::text) OR
((codice_attivita)::text = '111'::text)))"
"
Rows Removed by Filter: 51569"
" ->
Materialize (cost=0.00..3671.21 rows=2 width=30) (actual time=0.000..0.008
rows=30 loops=3250)"
" ->
Seq Scan on mo_testata mot (cost=0.00..3671.20 rows=2 width=30) (actual
time=0.047..10.912 rows=30 loops=1)"
"
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)))"
"
Rows Removed by Filter: 55115"
" -> Hash (cost=5722.51..5722.51 rows=92 width=12)
(actual time=62.490..62.490 rows=4609 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 199kB"
" -> Subquery Scan on vischi1
(cost=5720.67..5722.51 rows=92 width=12) (actual time=57.722..61.138 rows=4609
loops=1)"
" -> HashAggregate
(cost=5720.67..5721.59 rows=92 width=19) (actual time=57.720..59.504 rows=4609
loops=1)"
" -> Index Scan using
idx_ap_preestazione_fk on age_prenotazione age (cost=0.43..5713.77 rows=920
width=19) (actual time=0.077..53.997 rows=4742 loops=1)"
" Index Cond:
((prestazione_fk)::text = 'CONSULT01'::text)"
" Filter: ((stato)::text = ANY
('{C,P,VIS}'::text[]))"
" Rows Removed by Filter: 4052"
" -> Hash (cost=5722.51..5722.51 rows=92 width=12) (actual
time=19.899..19.899 rows=1584 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 69kB"
" -> Subquery Scan on vischi2 (cost=5720.67..5722.51
rows=92 width=12) (actual time=18.316..19.421 rows=1584 loops=1)"
" -> HashAggregate (cost=5720.67..5721.59
rows=92 width=19) (actual time=18.315..18.878 rows=1584 loops=1)"
" -> Index Scan using
idx_ap_preestazione_fk on age_prenotazione age_1 (cost=0.43..5713.77 rows=920
width=19) (actual time=0.777..16.969 rows=1719 loops=1)"
" Index Cond:
((prestazione_fk)::text = 'CONTR6MESI'::text)"
" Filter: ((stato)::text = ANY
('{C,P,VIS}'::text[]))"
" Rows Removed by Filter: 989"
" -> Hash (cost=5722.51..5722.51 rows=92 width=12) (actual
time=11.113..11.113 rows=1055 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 46kB"
" -> Subquery Scan on vischi3 (cost=5720.67..5722.51
rows=92 width=12) (actual time=10.112..10.797 rows=1055 loops=1)"
" -> HashAggregate (cost=5720.67..5721.59 rows=92
width=19) (actual time=10.111..10.415 rows=1055 loops=1)"
" -> Index Scan using idx_ap_preestazione_fk on
age_prenotazione age_2 (cost=0.43..5713.77 rows=920 width=19) (actual
time=0.047..9.182 rows=1153 loops=1)"
" Index Cond: ((prestazione_fk)::text =
'CONTR12MESI01'::text)"
" Filter: ((stato)::text = ANY
('{C,P,VIS}'::text[]))"
" Rows Removed by Filter: 851"
"Total runtime: 454174.561 ms"PG 10 (76 minuti) Explain analyze
"GroupAggregate (cost=258359.45..258373.75 rows=110 width=509) (actual
time=4562350.968..4562351.066 rows=30 loops=1)"
" 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,
mot.data_intervento, (count(*)), (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 ((CURRENT_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, (max(age.data_inizio)),
(max(age_1.data_inizio)), motb.note, (CASE WHEN (mot.sconto_autotrapianto = 0)
THEN 'Nessuno'::text WHEN (mot.sconto_autotrapianto = 1) THEN 'SANDERS
250'::text WHEN (mot.sconto_autotrapianto = 2) THEN 'PARRUCCHIERI 250'::text
WHEN (mot.sconto_autotrapianto = 3) THEN 'SANDERS 500'::text ELSE NULL::text
END), mot.codice_attivita, (sum((sum(pid.quantita)))),
cli.statosoggettotrapianto"
" -> Sort (cost=258359.45..258359.72 rows=110 width=509) (actual
time=4562350.944..4562350.956 rows=58 loops=1)"
" 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,
mot.data_intervento, (count(*)), (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 ((CURRENT_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, (max(age.data_inizio)),
(max(age_1.data_inizio)), motb.note, (CASE WHEN (mot.sconto_autotrapianto = 0)
THEN 'Nessuno'::text WHEN (mot.sconto_autotrapianto = 1) THEN 'SANDERS
250'::text WHEN (mot.sconto_autotrapianto = 2) THEN 'PARRUCCHIERI 250'::text
WHEN (mot.sconto_autotrapianto = 3) THEN 'SANDERS 500'::text ELSE NULL::text
END), mot.codice_attivita, (sum((sum(pid.quantita)))),
cli.statosoggettotrapianto"
" Sort Method: quicksort Memory: 45kB"
" -> Hash Left Join (cost=248267.27..258355.72 rows=110 width=509)
(actual time=137581.886..4562350.526 rows=58 loops=1)"
" Hash Cond: (cli.id = vischi1.cliente)"
" -> Merge Left Join (cost=224667.89..234692.37 rows=110
width=436) (actual time=137562.992..4562331.302 rows=58 loops=1)"
" Merge Cond: (cli.id = age_1.soggetto)"
" -> Merge Left Join (cost=218662.39..228652.04 rows=110
width=428) (actual time=137559.881..4562326.800 rows=58 loops=1)"
" Merge Cond: (cli.id = age.soggetto)"
" -> Nested Loop Left Join
(cost=210701.31..220644.67 rows=110 width=420) (actual
time=137555.476..4562320.085 rows=58 loops=1)"
" Join Filter: ((min(mot.id)) =
mor.modulo_ordine)"
" Rows Removed by Join Filter: 198662"
" -> Nested Loop Left Join
(cost=196095.49..202452.97 rows=1 width=349) (actual
time=137378.351..4557616.865 rows=30 loops=1)"
" Join Filter: (mott_1.cliente = cli.id)"
" Rows Removed by Join Filter: 161201"
" -> Nested Loop Left Join
(cost=192935.34..198934.87 rows=1 width=317) (actual
time=137339.212..4557488.278 rows=30 loops=1)"
" Join Filter: (mott.cliente =
cli.id)"
" Rows Removed by Join Filter:
249177"
" -> Merge Left Join
(cost=189783.67..195472.43 rows=1 width=285) (actual
time=137298.415..4557310.522 rows=30 loops=1)"
" Merge Cond: (cli.id =
kbtc4.cliente)"
" -> Nested Loop Left Join
(cost=169488.66..175177.32 rows=1 width=253) (actual
time=137220.694..4557232.655 rows=30 loops=1)"
" Join Filter:
(kbtc3.cliente = cli.id)"
" Rows Removed by Join
Filter: 285589"
" -> Nested Loop Left
Join (cost=115582.24..120682.71 rows=1 width=245) (actual
time=136872.390..4556747.696 rows=30 loops=1)"
" Join Filter:
(kbtc2.cliente = cli.id)"
" Rows Removed by
Join Filter: 287600"
" -> Nested Loop
(cost=61614.65..66126.93 rows=1 width=237) (actual time=136515.267..4556249.912
rows=30 loops=1)"
" Join
Filter: (cli.id = mot.cliente)"
" Rows
Removed by Join Filter: 251640"
" ->
GroupAggregate (cost=7155.53..7155.57 rows=1 width=43) (actual
time=54.720..54.992 rows=30 loops=1)"
"
Group Key: mot.cliente, mot.data_intervento, (count(*)),
mot.sconto_autotrapianto, mot.codice_attivita, mot.id_mo"
" ->
Sort (cost=7155.53..7155.54 rows=1 width=39) (actual time=54.714..54.767
rows=30 loops=1)"
"
Sort Key: mot.cliente, mot.data_intervento, (count(*)),
mot.sconto_autotrapianto, mot.codice_attivita, mot.id_mo"
"
Sort Method: quicksort Memory: 27kB"
"
-> Hash Join (cost=7051.05..7155.52 rows=1 width=39) (actual
time=52.837..54.687 rows=30 loops=1)"
"
Hash Cond: ((mo_testata.cliente = mot.cliente) AND
((mo_testata.codice_attivita)::text = (mot.codice_attivita)::text))"
"
-> HashAggregate (cost=3402.60..3437.42 rows=3482 width=16) (actual
time=29.549..30.639 rows=3184 loops=1)"
"
Group Key: mo_testata.cliente, mo_testata.codice_attivita"
"
-> Seq Scan on mo_testata (cost=0.00..3375.54 rows=3608 width=8)
(actual time=0.025..27.976 rows=3504 loops=1)"
"
Filter: (((stato_contratto)::text <> 'AN'::text) AND
(((codice_attivita)::text = '100'::text) OR ((codice_attivita)::text =
'110'::text) OR ((codice_attivita)::text = '111'::text)))"
"
Rows Removed by Filter: 51068"
"
-> Hash (cost=3648.43..3648.43 rows=2 width=31) (actual
time=23.257..23.257 rows=30 loops=1)"
"
Buckets: 1024 Batches: 1 Memory Usage: 10kB"
"
-> Seq Scan on mo_testata mot (cost=0.00..3648.43 rows=2
width=31) (actual time=0.074..23.234 rows=30 loops=1)"
"
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)))"
"
Rows Removed by Filter: 54542"
" -> Nested
Loop Left Join (cost=54459.12..58971.34 rows=1 width=225) (actual
time=63.494..151868.179 rows=8389 loops=30)"
" Join
Filter: (kbtc1.cliente = cli.id)"
" Rows
Removed by Join Filter: 91003264"
" ->
Nested Loop (cost=7820.17..11797.19 rows=1 width=217) (actual
time=54.499..101854.971 rows=8389 loops=30)"
"
-> Nested Loop (cost=7819.88..11796.75 rows=1 width=167) (actual
time=54.484..101788.132 rows=8389 loops=30)"
"
Join Filter: (cli.id = mota.cliente)"
"
Rows Removed by Join Filter: 210465014"
"
-> Nested Loop (cost=4280.93..7943.63 rows=1 width=163) (actual
time=3.135..679.314 rows=28990 loops=30)"
"
-> Nested Loop (cost=4280.51..7943.07 rows=1 width=145) (actual
time=3.129..525.102 rows=30072 loops=30)"
"
-> Nested Loop (cost=4280.09..7942.51 rows=1 width=127)
(actual time=3.126..397.681 rows=30079 loops=30)"
"
Join Filter: (motd.cliente = cli.id)"
"
-> Hash Join (cost=4279.66..7941.73 rows=1 width=55)
(actual time=3.115..127.716 rows=30079 loops=30)"
"
Hash Cond: ((motb.cliente = motd.cliente) AND
(motb.id = motd.id))"
"
-> Seq Scan on mo_testata motb
(cost=0.00..2829.77 rows=54577 width=55) (actual time=0.002..25.429 rows=54572
loops=30)"
"
-> Hash (cost=3883.23..3883.23 rows=26429
width=8) (actual time=93.251..93.251 rows=30079 loops=1)"
"
Buckets: 32768 Batches: 1 Memory Usage:
1431kB"
"
-> Subquery Scan on motd
(cost=3354.65..3883.23 rows=26429 width=8) (actual time=59.276..82.758
rows=30079 loops=1)"
"
-> HashAggregate
(cost=3354.65..3618.94 rows=26429 width=8) (actual time=59.275..70.716
rows=30079 loops=1)"
"
Group Key: motc.cliente"
"
-> Seq Scan on mo_testata
motc (cost=0.00..3102.65 rows=50399 width=8) (actual time=0.003..37.054
rows=50527 loops=1)"
"
Filter:
(((stato_contratto)::text <> 'AN'::text) AND ((codice_attivita)::text <>
'100'::text))"
"
Rows Removed by Filter:
4045"
"
-> Index Scan using banksubject_pkey on banksubject
cli (cost=0.42..0.76 rows=1 width=72) (actual time=0.007..0.007 rows=1
loops=902370)"
"
Index Cond: (id = motb.cliente)"
"
-> Index Scan using banksubject_pkey on banksubject fil
(cost=0.42..0.56 rows=1 width=26) (actual time=0.003..0.003 rows=1
loops=902370)"
"
Index Cond: (id = cli.filiale_ass)"
"
-> Index Scan using banksubject_pkey on banksubject tri
(cost=0.42..0.56 rows=1 width=26) (actual time=0.004..0.004 rows=1
loops=902160)"
"
Index Cond: (id = cli.profes_ass)"
"
-> HashAggregate (cost=3538.95..3635.62 rows=9667 width=4) (actual
time=0.001..1.980 rows=7260 loops=869700)"
"
Group Key: mota.cliente"
"
-> Seq Scan on mo_testata mota (cost=0.00..3511.98 rows=10787
width=4) (actual time=0.017..32.086 rows=11262 loops=1)"
"
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)))"
"
Rows Removed by Filter: 43310"
"
-> Index Scan using c_lookup_pkey on c_lookup clp (cost=0.29..0.43 rows=1
width=58) (actual time=0.005..0.005 rows=1 loops=251670)"
"
Index Cond: (id = motb.mo_type)"
" ->
HashAggregate (cost=46638.95..46803.63 rows=16468 width=12) (actual
time=0.001..3.687 rows=10849 loops=251670)"
"
Group Key: kbtc1.cliente"
"
-> Bitmap Heap Scan on kb_testata kbtc1 (cost=802.88..46425.75 rows=42640
width=12) (actual time=7.782..44.001 rows=44692 loops=1)"
"
Recheck Cond: (consegna IS NOT NULL)"
"
Heap Blocks: exact=19348"
"
-> Bitmap Index Scan on kb_testata_consegna_key (cost=0.00..792.22
rows=42640 width=0) (actual time=4.984..4.984 rows=44695 loops=1)"
"
Index Cond: (consegna IS NOT NULL)"
" -> Finalize
HashAggregate (cost=53967.59..54148.57 rows=18098 width=12) (actual
time=11.603..14.590 rows=9588 loops=30)"
" Group Key:
kbtc2.cliente"
" -> Gather
(cost=49986.03..53786.61 rows=36196 width=12) (actual time=288.648..324.601
rows=56500 loops=1)"
"
Workers Planned: 2"
"
Workers Launched: 2"
" ->
Partial HashAggregate (cost=48986.03..49167.01 rows=18098 width=12) (actual
time=282.786..290.406 rows=18833 loops=3)"
"
Group Key: kbtc2.cliente"
"
-> Parallel Seq Scan on kb_testata kbtc2 (cost=0.00..48137.47 rows=169713
width=12) (actual time=0.006..201.213 rows=135455 loops=3)"
"
Filter: (visita IS NOT NULL)"
"
Rows Removed by Filter: 202417"
" -> Finalize
HashAggregate (cost=53906.42..54087.40 rows=18098 width=12) (actual
time=11.293..14.203 rows=9521 loops=30)"
" Group Key:
kbtc3.cliente"
" -> Gather
(cost=49924.86..53725.44 rows=36196 width=12) (actual time=277.585..314.035
rows=58119 loops=1)"
" Workers
Planned: 2"
" Workers
Launched: 2"
" ->
Partial HashAggregate (cost=48924.86..49105.84 rows=18098 width=12) (actual
time=272.863..280.570 rows=19373 loops=3)"
"
Group Key: kbtc3.cliente"
" ->
Parallel Seq Scan on kb_testata kbtc3 (cost=0.00..48137.47 rows=157479
width=12) (actual time=0.007..190.038 rows=126757 loops=3)"
"
Filter: (prodotto_igienico IS NOT NULL)"
"
Rows Removed by Filter: 211115"
" -> GroupAggregate
(cost=20295.00..20295.06 rows=3 width=36) (actual time=77.715..77.765 rows=10
loops=1)"
" Group Key:
kbtc4.cliente"
" -> Sort
(cost=20295.00..20295.01 rows=3 width=12) (actual time=77.702..77.711 rows=10
loops=1)"
" Sort Key:
kbtc4.cliente"
" Sort Method:
quicksort Memory: 25kB"
" -> Append
(cost=19368.33..20294.98 rows=3 width=12) (actual time=73.642..77.673 rows=10
loops=1)"
" ->
GroupAggregate (cost=19368.33..19368.35 rows=1 width=12) (actual
time=73.641..73.648 rows=10 loops=1)"
"
Group Key: kbtc4.cliente"
" ->
Sort (cost=19368.33..19368.34 rows=1 width=8) (actual time=73.628..73.635
rows=10 loops=1)"
"
Sort Key: kbtc4.cliente"
"
Sort Method: quicksort Memory: 25kB"
"
-> Nested Loop (cost=1000.42..19368.32 rows=1 width=8) (actual
time=62.377..73.606 rows=10 loops=1)"
"
-> Gather (cost=1000.00..19359.88 rows=1 width=8) (actual
time=62.340..73.507 rows=10 loops=1)"
"
Workers Planned: 2"
"
Workers Launched: 2"
"
-> Parallel Seq Scan on kb_prodotti_igienici_dettaglio pid
(cost=0.00..18359.78 rows=1 width=8) (actual time=57.866..67.904 rows=3
loops=3)"
"
Filter: prod_tricopigmentazione"
"
Rows Removed by Filter: 263179"
"
-> Index Scan using "idx_kt_prodotto igenico" on kb_testata kbtc4
(cost=0.42..8.44 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=10)"
"
Index Cond: (prodotto_igienico = pid.id_testata)"
" ->
GroupAggregate (cost=364.27..364.29 rows=1 width=12) (actual time=1.539..1.539
rows=0 loops=1)"
"
Group Key: kbtc4_1.cliente"
" ->
Sort (cost=364.27..364.28 rows=1 width=8) (actual time=1.537..1.537 rows=0
loops=1)"
"
Sort Key: kbtc4_1.cliente"
"
Sort Method: quicksort Memory: 25kB"
"
-> Nested Loop (cost=0.42..364.26 rows=1 width=8) (actual time=1.519..1.519
rows=0 loops=1)"
"
-> Seq Scan on kb_pagamento_contrassegno_prodotti_igienici_dettaglio
pid_1 (cost=0.00..355.82 rows=1 width=8) (actual time=1.518..1.518 rows=0
loops=1)"
"
Filter: prod_tricopigmentazione"
"
Rows Removed by Filter: 10182"
"
-> Index Scan using
kb_testata_pagamento_contrassegno_prodotto_igienico_key on kb_testata kbtc4_1
(cost=0.42..8.44 rows=1 width=8) (never executed)"
"
Index Cond: (pagamento_contrassegno_prodotto_igienico =
pid_1.id_testata)"
" ->
GroupAggregate (cost=562.28..562.31 rows=1 width=12) (actual time=2.478..2.478
rows=0 loops=1)"
"
Group Key: kbtc4_2.cliente"
" ->
Sort (cost=562.28..562.29 rows=1 width=8) (actual time=2.477..2.477 rows=0
loops=1)"
"
Sort Key: kbtc4_2.cliente"
"
Sort Method: quicksort Memory: 25kB"
"
-> Nested Loop (cost=0.42..562.27 rows=1 width=8) (actual time=2.470..2.470
rows=0 loops=1)"
"
-> Seq Scan on kb_nota_credito_dettaglio pid_2 (cost=0.00..553.83
rows=1 width=8) (actual time=2.469..2.469 rows=0 loops=1)"
"
Filter: prod_tricopigmentazione"
"
Rows Removed by Filter: 9487"
"
-> Index Scan using kb_testata_nota_credito_key on kb_testata kbtc4_2
(cost=0.42..8.44 rows=1 width=8) (never executed)"
"
Index Cond: (nota_credito = pid_2.id_testata)"
" -> HashAggregate
(cost=3151.68..3262.66 rows=8879 width=36) (actual time=1.186..4.197 rows=8306
loops=30)"
" Group Key: mott.cliente"
" -> Seq Scan on mo_testata
mott (cost=0.00..3102.65 rows=9804 width=4) (actual time=0.010..31.034
rows=9911 loops=1)"
" Filter:
((codice_attivita)::text = ANY ('{006,060,603,610}'::text[]))"
" Rows Removed by
Filter: 44661"
" -> HashAggregate
(cost=3160.15..3287.99 rows=10227 width=36) (actual time=1.205..3.169 rows=5374
loops=30)"
" Group Key: mott_1.cliente"
" -> Seq Scan on mo_testata mott_1
(cost=0.00..3102.65 rows=11499 width=4) (actual time=0.010..31.526 rows=11471
loops=1)"
" Filter:
((codice_attivita)::text = ANY ('{003,030,303,305}'::text[]))"
" Rows Removed by Filter:
43101"
" -> Finalize GroupAggregate
(cost=14605.82..17698.82 rows=21906 width=81) (actual time=138.389..155.101
rows=6624 loops=30)"
" Group Key: mor.modulo_ordine, mor.id_mo,
mor.type_rata"
" -> Gather Merge
(cost=14605.82..16941.22 rows=18256 width=49) (actual time=138.376..146.947
rows=6626 loops=30)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Partial GroupAggregate
(cost=13605.80..13834.00 rows=9128 width=49) (actual time=132.559..135.936
rows=2209 loops=90)"
" Group Key:
mor.modulo_ordine, mor.id_mo, mor.type_rata"
" -> Sort
(cost=13605.80..13628.62 rows=9128 width=23) (actual time=132.545..133.222
rows=2210 loops=90)"
" Sort Key:
mor.modulo_ordine, mor.id_mo, mor.type_rata"
" Sort Method: quicksort
Memory: 321kB"
" -> Hash Join
(cost=3420.64..13005.35 rows=9128 width=23) (actual time=37.083..130.708
rows=2210 loops=90)"
" Hash Cond:
(mor.modulo_ordine = mort.id)"
" -> Parallel Seq
Scan on mo_rata mor (cost=0.00..7841.66 rows=138066 width=23) (actual
time=0.004..41.758 rows=110450 loops=90)"
" -> Hash
(cost=3375.54..3375.54 rows=3608 width=4) (actual time=54.289..54.289 rows=3504
loops=61)"
" Buckets:
4096 Batches: 1 Memory Usage: 156kB"
" -> Seq
Scan on mo_testata mort (cost=0.00..3375.54 rows=3608 width=4) (actual
time=0.025..51.777 rows=3504 loops=61)"
"
Filter: (((stato_contratto)::text <> 'AN'::text) AND (((codice_attivita)::text
= '100'::text) OR ((codice_attivita)::text = '110'::text) OR
((codice_attivita)::text = '111'::text)))"
" Rows
Removed by Filter: 51068"
" -> Materialize (cost=7961.07..8003.81 rows=1313
width=12) (actual time=3.941..6.223 rows=1555 loops=1)"
" -> GroupAggregate (cost=7961.07..7987.39
rows=1313 width=19) (actual time=3.934..5.494 rows=1555 loops=1)"
" Group Key: age.soggetto,
age.prestazione_fk"
" -> Sort (cost=7961.07..7964.37
rows=1319 width=19) (actual time=3.929..4.441 rows=1686 loops=1)"
" Sort Key: age.soggetto"
" Sort Method: quicksort Memory:
180kB"
" -> Index Scan using
idx_ap_preestazione_fk on age_prenotazione age (cost=0.43..7892.71 rows=1319
width=19) (actual time=0.040..3.170 rows=1686 loops=1)"
" Index Cond:
((prestazione_fk)::text = 'CONTR6MESI'::text)"
" Filter: ((stato)::text = ANY
('{C,P,VIS}'::text[]))"
" Rows Removed by Filter: 972"
" -> Materialize (cost=6005.50..6037.58 rows=986 width=12)
(actual time=2.663..4.163 rows=1039 loops=1)"
" -> GroupAggregate (cost=6005.50..6025.25 rows=986
width=19) (actual time=2.661..3.682 rows=1038 loops=1)"
" Group Key: age_1.soggetto,
age_1.prestazione_fk"
" -> Sort (cost=6005.50..6007.97 rows=989
width=19) (actual time=2.657..2.963 rows=1134 loops=1)"
" Sort Key: age_1.soggetto"
" Sort Method: quicksort Memory: 137kB"
" -> Index Scan using
idx_ap_preestazione_fk on age_prenotazione age_1 (cost=0.43..5956.30 rows=989
width=19) (actual time=0.024..2.163 rows=1134 loops=1)"
" Index Cond:
((prestazione_fk)::text = 'CONTR12MESI01'::text)"
" Filter: ((stato)::text = ANY
('{C,P,VIS}'::text[]))"
" Rows Removed by Filter: 829"
" -> Hash (cost=23544.24..23544.24 rows=4411 width=12) (actual
time=18.846..18.846 rows=4564 loops=1)"
" Buckets: 8192 Batches: 1 Memory Usage: 261kB"
" -> Subquery Scan on vischi1 (cost=23456.02..23544.24
rows=4411 width=12) (actual time=14.255..17.504 rows=4564 loops=1)"
" -> HashAggregate (cost=23456.02..23500.13
rows=4411 width=19) (actual time=14.254..15.674 rows=4564 loops=1)"
" Group Key: age_2.soggetto,
age_2.prestazione_fk"
" -> Bitmap Heap Scan on age_prenotazione age_2
(cost=168.51..23422.47 rows=4474 width=19) (actual time=2.734..11.928
rows=4697 loops=1)"
" Recheck Cond: ((prestazione_fk)::text =
'CONSULT01'::text)"
" Filter: ((stato)::text = ANY
('{C,P,VIS}'::text[]))"
" Rows Removed by Filter: 3982"
" Heap Blocks: exact=6358"
" -> Bitmap Index Scan on
idx_ap_preestazione_fk (cost=0.00..167.40 rows=8929 width=0) (actual
time=1.856..1.856 rows=8679 loops=1)"
" Index Cond:
((prestazione_fk)::text = 'CONSULT01'::text)"
"Planning time: 12.348 ms"
"Execution time: 4562353.071 ms"
Other related posts:
