Ok, here are the last rows for the vacuum analyze verbose
INFO: free space map contains 154679 pages in 39 relations
DETAIL: A total of 126176 page slots are in use (including overhead).
126176 page slots are required to track all free space.
Current limits are: 160000 page slots, 5000 relations, using 1476 kB.
L'interrogazione è stata eseguita con successo, ma senza risultato, in
1332269 ms.
and I attach the complete explain analyze of the complex query.
Giving more detail about the tables involved in the query could be not
so easy as they are a lot.
The joins are made between columns that are primary key in a table and
indexed in the other.
All the where clausole are on indexed colums (perhaps there are too many
indexes...)
Thanks a lot.
QUERY PLAN
Merge Right Join (cost=508603077.17..508603195.59 rows=1 width=227) (actual time=73312.340..1463106.860 rows=32407 loops=1)
Merge Cond: (ve_edil_rendite.id_domanda = domande.id_domanda)
-> GroupAggregate (cost=0.00..105.51 rows=1031 width=11) (actual time=0.098..110.794 rows=1031 loops=1)
-> Index Scan using pk_ve_edil_rendite on ve_edil_rendite (cost=0.00..86.84 rows=1157 width=11) (actual time=0.063..98.601 rows=1157 loops=1)
-> Materialize (cost=508603077.17..508603077.18 rows=1 width=195) (actual time=73312.188..1462604.387 rows=32407 loops=1)
-> Nested Loop (cost=506932259.90..508603077.17 rows=1 width=195) (actual time=73312.174..1462385.266 rows=32407 loops=1)
-> Merge Join (cost=406932259.90..408603074.89 rows=1 width=188) (actual time=73312.123..1461834.776 rows=32407 loops=1)
Merge Cond: (domande.id_domanda = c_elaout_7.id_domanda)
-> Merge Join (cost=406932259.90..408188339.97 rows=1 width=240) (actual time=72975.426..1458427.886 rows=32407 loops=1)
Merge Cond: (c_elaout_5.id_domanda = domande.id_domanda)
-> Merge Join (cost=3895.15..1259628.81 rows=138561 width=41) (actual time=1721.643..7493.711 rows=99308 loops=1)
Merge Cond: (edil_veneto.id_domanda = c_elaout_5.id_domanda)
-> Merge Join (cost=1123.18..372710.75 rows=98122 width=29) (actual time=569.693..4135.019 rows=99308 loops=1)
Merge Cond: (edil_veneto.id_domanda = c_elaout_6.id_domanda)
-> Index Scan using "IDX_pk_Edil_Veneto" on edil_veneto (cost=0.00..11825.14 rows=232649 width=17) (actual time=0.080..1157.486 rows=232471 loops=1)
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_6 (cost=0.00..359914.34 rows=98122 width=12) (actual time=0.094..1900.373 rows=99308 loops=1)
Index Cond: ((c_elaout_6.node)::text = 'contributo_sociale'::text)
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_5 (cost=0.00..887091.20 rows=245306 width=12) (actual time=0.120..2389.615 rows=250746 loops=1)
Index Cond: ((c_elaout_5.node)::text = 'contributo'::text)
-> Materialize (cost=406928364.74..406928364.75 rows=1 width=199) (actual time=69623.122..1450472.706 rows=32407 loops=1)
-> Nested Loop (cost=402583154.89..406928364.74 rows=1 width=199) (actual time=69623.107..1450215.911 rows=32407 loops=1)
Join Filter: ((r_enti.codice_ente)::text = (r_luoghi.cod_catastale)::text)
-> Merge Join (cost=202583154.89..206928031.60 rows=1 width=198) (actual time=69611.258..115367.182 rows=32407 loops=1)
Merge Cond: (domande.id_domanda = c_elaout_4.id_domanda)
-> Merge Join (cost=202583154.89..206425374.54 rows=1 width=186) (actual time=69007.657..113053.726 rows=32407 loops=1)
Merge Cond: (domande.id_domanda = c_elain_3.id_domanda)
-> Merge Join (cost=201328203.80..205170407.27 rows=41 width=138) (actual time=66160.710..100104.342 rows=32407 loops=1)
Merge Cond: (domande.id_domanda = c_elain_7.id_domanda)
-> Merge Join (cost=201328203.80..204498966.35 rows=93 width=126) (actual time=56792.251..72298.070 rows=32407 loops=1)
Merge Cond: (domande.id_domanda = c_elain_9.id_domanda)
-> Merge Join (cost=201322293.83..203828121.81 rows=424 width=114) (actual time=47349.082..55619.999 rows=32407 loops=1)
Merge Cond: (domande.id_domanda = c_elain_8.id_domanda)
-> Nested Loop (cost=201318498.02..203164011.74 rows=2431 width=102) (actual time=46694.249..53476.766 rows=32407 loops=1)
-> Merge Join (cost=101318498.02..103147289.10 rows=2431 width=79) (actual time=46694.174..52727.233 rows=32407 loops=1)
Merge Cond: (domande.id_domanda = doc.id)
-> Merge Join (cost=101318487.80..103060677.64 rows=2493 width=75) (actual time=39107.036..44181.695 rows=32485 loops=1)
Merge Cond: (domande.id_domanda = c_elain_1.id_domanda)
-> Merge Join (cost=101316002.90..102447327.03 rows=15480 width=63) (actual time=38448.534..42068.713 rows=32485 loops=1)
Merge Cond: (domande.id_domanda = c_elain.id_domanda)
-> Merge Join (cost=101314975.72..101780946.74 rows=88502 width=51) (actual time=37336.167..39802.740 rows=32485 loops=1)
Merge Cond: (c_elain_2.id_domanda = domande.id_domanda)
-> Index Scan using "IDX_1_c_elain" on c_elain c_elain_2 (cost=0.00..461104.96 rows=129806 width=12) (actual time=0.132..2743.907 rows=124937 loops=1)
Index Cond: ((node)::text = 'N_componenti'::text)
-> Sort (cost=101314967.66..101316800.15 rows=732995 width=39) (actual time=36522.518..36608.379 rows=33296 loops=1)
Sort Key: domande.id_domanda
Sort Method: quicksort Memory: 4243kB
-> Merge Join (cost=119414.31..1243561.32 rows=732995 width=39) (actual time=6522.654..36414.007 rows=33296 loops=1)
Merge Cond: (domande.id_dichiarazione = generiche_data_nascita_piu_anziano.id_dichiarazione)
-> Merge Join (cost=18770.82..1126115.64 rows=123933 width=39) (actual time=2734.720..17725.205 rows=33296 loops=1)
Merge Cond: (domande.id_dichiarazione = c_elaout.id_domanda)
-> Index Scan using "IDX_5_domande" on domande (cost=0.00..91684.40 rows=31967 width=27) (actual time=0.089..129.290 rows=33304 loops=1)
Index Cond: (id_servizio = 11002)
Filter: (id_ente > 0)
-> Index Scan using "IDX_2_c_elaout" on c_elaout (cost=0.00..1031179.16 rows=805279 width=12) (actual time=0.280..15996.355 rows=734473 loops=1)
Filter: ((c_elaout.node)::text = 'ISEE'::text)
-> Materialize (cost=100643.49..106653.58 rows=601009 width=12) (actual time=0.128..16933.321 rows=809362 loops=1)
-> Subquery Scan generiche_data_nascita_piu_anziano (cost=0.00..100042.48 rows=601009 width=12) (actual time=0.119..13705.248 rows=808813 loops=1)
-> GroupAggregate (cost=0.00..94032.39 rows=601009 width=12) (actual time=0.113..10692.362 rows=808813 loops=1)
-> Index Scan using "IDX_1_componenti" on componenti (cost=0.00..76403.45 rows=2023265 width=12) (actual time=0.073..4578.003 rows=2001629 loops=1)
-> Index Scan using "IDX_1_c_elain" on c_elain (cost=0.00..665581.51 rows=188052 width=12) (actual time=0.109..1686.769 rows=192306 loops=1)
Index Cond: ((c_elain.node)::text = 'VSE'::text)
-> Index Scan using "IDX_1_c_elain" on c_elain c_elain_1 (cost=0.00..613000.48 rows=173074 width=12) (actual time=0.096..1597.267 rows=166297 loops=1)
Index Cond: ((c_elain_1.node)::text = 'AffittoISEE'::text)
-> Index Scan using pk_doc on doc (cost=0.00..81963.12 rows=1847118 width=4) (actual time=0.080..4837.759 rows=1819225 loops=1)
Filter: (doc.id_tp_stato_doc = 1)
-> Index Scan using "IDX_pk_R_Enti" on r_enti (cost=0.00..6.87 rows=1 width=31) (actual time=0.010..0.013 rows=1 loops=32407)
Index Cond: (r_enti.id_ente = domande.id_ente)
-> Index Scan using "IDX_1_c_elain" on c_elain c_elain_8 (cost=0.00..663631.02 rows=187497 width=12) (actual time=0.070..1626.606 rows=168233 loops=1)
Index Cond: ((c_elain_8.node)::text = 'Spese'::text)
-> Index Scan using "IDX_2_c_elain" on c_elain c_elain_9 (cost=0.00..670253.16 rows=235758 width=12) (actual time=0.146..16050.705 rows=224093 loops=1)
Filter: ((c_elain_9.node)::text = 'Mesi'::text)
-> Index Scan using "IDX_2_c_elain" on c_elain c_elain_7 (cost=0.00..670253.16 rows=474845 width=12) (actual time=0.072..26798.096 rows=425080 loops=1)
Filter: ((c_elain_7.node)::text = 'Affitto'::text)
-> Materialize (cost=1254951.09..1254963.95 rows=1286 width=48) (actual time=1450.226..12617.244 rows=63927 loops=1)
-> Merge Join (cost=2423.84..1254949.80 rows=1286 width=48) (actual time=1450.215..12289.021 rows=63927 loops=1)
Merge Cond: (c_elain_3.id_domanda = c_elaout_1.id_domanda)
-> Merge Join (cost=1094.64..606811.53 rows=1492 width=36) (actual time=465.516..9248.961 rows=63927 loops=1)
Merge Cond: (c_elain_3.id_domanda = c_elaout_3.id_domanda)
-> Merge Join (cost=224.20..182997.39 rows=2667 width=24) (actual time=0.237..7239.010 rows=63927 loops=1)
Merge Cond: (c_elain_3.id_domanda = c_elaout_2.id_domanda)
-> Index Scan using "IDX_1_c_elain" on c_elain c_elain_3 (cost=0.00..74101.14 rows=19621 width=12) (actual time=0.118..2866.345 rows=63976 loops=1)
Index Cond: ((node)::text = 'Solo_anziani'::text)
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_2 (cost=0.00..108761.74 rows=28155 width=12) (actual time=0.097..3893.464 rows=63927 loops=1)
Index Cond: ((c_elaout_2.node)::text = 'ise_fsa'::text)
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_3 (cost=0.00..423543.07 rows=115886 width=12) (actual time=0.096..1478.403 rows=105705 loops=1)
Index Cond: ((c_elaout_3.node)::text = 'incidenza'::text)
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_1 (cost=0.00..647740.85 rows=178481 width=12) (actual time=0.087..2380.458 rows=166285 loops=1)
Index Cond: ((c_elaout_1.node)::text = 'isee_fsa'::text)
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_4 (cost=0.00..502312.35 rows=137879 width=12) (actual time=0.104..1847.028 rows=135698 loops=1)
Index Cond: ((c_elaout_4.node)::text = 'esito'::text)
-> Seq Scan on r_luoghi (cost=100000000.00..100000200.84 rows=10584 width=11) (actual time=0.006..19.767 rows=10584 loops=32407)
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_7 (cost=0.00..414451.53 rows=113348 width=12) (actual time=0.100..3023.961 rows=99308 loops=1)
Index Cond: ((c_elaout_7.node)::text = 'contributo_regolare'::text)
-> Index Scan using "IDX_pk_VE_EDIL_tp_superfici" on ve_edil_tp_superfici (cost=0.00..2.27 rows=1 width=11) (actual time=0.006..0.008 rows=1 loops=32407)
Index Cond: (ve_edil_tp_superfici.id_tp_superficie = edil_veneto.id_tp_superficie)
Total runtime: 1463175.762 ms
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
