> Could you show us the result of SELECT version(); ?
of course I can
PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.3.2 [gcc-4_3-branch revision 141291]
>
> Have you done any VACUUM VERBOSE lately and captured the output? If
> so, what do the last few lines say? (That's a lot of relations for
> the number of pages; just curious how it maps to actual.)
It need a lot of time (20 GB database), when I will have the answare
I'll post it
>
> > enable_hashjoin = off
> > enable_nestloop = off
> > enable_seqscan = off
> > enable_sort = off
>
> That's probably a bad idea. If particular queries aren't performing
> well, you can always set these temporarily on a particular connection.
> Even then, turning these off is rarely a good idea except for
> diagnostic purposes. I *strongly* recommend you put all of these back
> to the defaults of 'on' and start from there, turning off selected
> items as needed to get EXPLAIN ANALYZE output to demonstrate the
> better plans you've found for particular queries.
OK, it will became the viceversa of what I'm doing now (set them to on
and set them to off only on the appropriate connection instead of set
them to off and set them to on only on some appropriate connection).
But the question is: do you thing it is impossible to find a
configuration that works fine for both the kind of query? The
application have to run even versus oracle db... i wont have to write a
different source for the two database...
>
> > effective_cache_size = 3600MB
>
> That seems a little on the low side for an 8GB machine, unless you
> have other things on there using a lot of RAM. Do you?
yes there are two instances of postgress running on the same server (the
database have to stay complitely separated).
>
> If you could set the optimizer options back on and get new plans where
> you show specifically which options (if any) where turned off for the
> run, that would be good. Also, please attach the plans to the email
> instead of pasting -- the word wrap makes them hard to read. Finally,
> if you could do \d on the tables involved in the query, it would help.
> I'll hold off looking at these in hopes that you can do the above.
>
> -Kevin
I attach the explanation of the log query after setting all the enable
to on. In this condition the query will never finish...
QUERY PLAN
Nested Loop Left Join (cost=283253.73..417552.90 rows=1 width=227)
Join Filter: (ve_edil_rendite.id_domanda = domande.id_domanda)
-> Nested Loop (cost=283222.38..417485.46 rows=1 width=195)
-> Nested Loop (cost=283222.38..417470.91 rows=1 width=247)
-> Nested Loop (cost=283222.38..417456.36 rows=1 width=235)
Join Filter: (edil_veneto.id_tp_superficie = ve_edil_tp_superfici.id_tp_superficie)
-> Nested Loop (cost=283222.38..417455.29 rows=1 width=228)
-> Nested Loop (cost=283222.38..417440.75 rows=1 width=216)
Join Filter: ((r_enti.codice_ente)::text = (r_luoghi.cod_catastale)::text)
-> Nested Loop (cost=283222.38..417107.61 rows=1 width=215)
-> Nested Loop (cost=283222.38..417093.06 rows=1 width=203)
-> Nested Loop (cost=283222.38..417084.75 rows=1 width=186)
-> Nested Loop (cost=283222.38..417070.20 rows=1 width=174)
-> Nested Loop (cost=283222.38..417057.25 rows=1 width=162)
-> Nested Loop (cost=283222.38..417044.33 rows=1 width=150)
-> Nested Loop (cost=283222.38..417031.40 rows=1 width=138)
-> Nested Loop (cost=283222.38..417016.85 rows=1 width=126)
-> Nested Loop (cost=283222.38..416343.12 rows=44 width=114)
-> Merge Join (cost=283222.38..384803.94 rows=2431 width=102)
Merge Cond: (componenti.id_dichiarazione = domande.id_dichiarazione)
-> GroupAggregate (cost=0.00..94032.39 rows=601009 width=12)
-> Index Scan using "IDX_1_componenti" on componenti (cost=0.00..76403.45 rows=2023265 width=12)
-> Sort (cost=283222.38..283223.41 rows=412 width=102)
Sort Key: domande.id_dichiarazione
-> Nested Loop (cost=37777.86..283204.48 rows=412 width=102)
-> Hash Join (cost=37777.86..281453.46 rows=106 width=90)
Hash Cond: (domande.id_ente = r_enti.id_ente)
-> Nested Loop (cost=37682.56..281356.58 rows=106 width=67)
-> Nested Loop (cost=37682.56..280435.24 rows=109 width=63)
-> Nested Loop (cost=37682.56..272346.69 rows=621 width=51)
-> Hash Join (cost=37682.56..222416.32 rows=3860 width=39)
Hash Cond: (c_elain_2.id_domanda = domande.id_domanda)
-> Bitmap Heap Scan on c_elain c_elain_2 (cost=2740.46..185813.04 rows=129806 width=12)
Recheck Cond: ((node)::text = 'N_componenti'::text)
-> Bitmap Index Scan on "IDX_3_c_elain" (cost=0.00..2708.01 rows=129806 width=0)
Index Cond: ((node)::text = 'N_componenti'::text)
-> Hash (cost=34542.51..34542.51 rows=31967 width=27)
-> Bitmap Heap Scan on domande (cost=600.64..34542.51 rows=31967 width=27)
Recheck Cond: (id_servizio = 11002)
Filter: (id_ente > 0)
-> Bitmap Index Scan on "IDX_7_domande" (cost=0.00..592.65 rows=32038 width=0)
Index Cond: (id_servizio = 11002)
-> Index Scan using "IDX_1_c_elain" on c_elain c_elain_1 (cost=0.00..12.92 rows=1 width=12)
Index Cond: (((c_elain_1.node)::text = 'AffittoISEE'::text) AND (c_elain_1.id_domanda = domande.id_domanda))
-> Index Scan using "IDX_1_c_elain" on c_elain (cost=0.00..13.01 rows=1 width=12)
Index Cond: (((c_elain.node)::text = 'VSE'::text) AND (c_elain.id_domanda = domande.id_domanda))
-> Index Scan using pk_doc on doc (cost=0.00..8.44 rows=1 width=4)
Index Cond: (doc.id = domande.id_domanda)
Filter: (doc.id_tp_stato_doc = 1)
-> Hash (cost=64.02..64.02 rows=2502 width=31)
-> Seq Scan on r_enti (cost=0.00..64.02 rows=2502 width=31)
-> Index Scan using "IDX_2_c_elaout" on c_elaout (cost=0.00..16.47 rows=4 width=12)
Index Cond: (c_elaout.id_domanda = domande.id_dichiarazione)
Filter: ((c_elaout.node)::text = 'ISEE'::text)
-> Index Scan using "IDX_1_c_elain" on c_elain c_elain_3 (cost=0.00..12.96 rows=1 width=12)
Index Cond: (((c_elain_3.node)::text = 'Solo_anziani'::text) AND (c_elain_3.id_domanda = domande.id_domanda))
-> Index Scan using "IDX_3_c_elaout" on c_elaout c_elaout_2 (cost=0.00..15.30 rows=1 width=12)
Index Cond: (((c_elaout_2.node)::text = 'ise_fsa'::text) AND (c_elaout_2.id_domanda = domande.id_domanda))
-> Index Scan using "IDX_2_c_elaout" on c_elaout c_elaout_3 (cost=0.00..14.54 rows=1 width=12)
Index Cond: (c_elaout_3.id_domanda = domande.id_domanda)
Filter: ((c_elaout_3.node)::text = 'incidenza'::text)
-> Index Scan using "IDX_2_c_elain" on c_elain c_elain_9 (cost=0.00..12.91 rows=1 width=12)
Index Cond: (c_elain_9.id_domanda = domande.id_domanda)
Filter: ((c_elain_9.node)::text = 'Mesi'::text)
-> Index Scan using "IDX_2_c_elain" on c_elain c_elain_8 (cost=0.00..12.91 rows=1 width=12)
Index Cond: (c_elain_8.id_domanda = domande.id_domanda)
Filter: ((c_elain_8.node)::text = 'Spese'::text)
-> Index Scan using "IDX_2_c_elain" on c_elain c_elain_7 (cost=0.00..12.91 rows=3 width=12)
Index Cond: (c_elain_7.id_domanda = domande.id_domanda)
Filter: ((c_elain_7.node)::text = 'Affitto'::text)
-> Index Scan using "IDX_2_c_elaout" on c_elaout c_elaout_1 (cost=0.00..14.54 rows=1 width=12)
Index Cond: (c_elaout_1.id_domanda = domande.id_domanda)
Filter: ((c_elaout_1.node)::text = 'isee_fsa'::text)
-> Index Scan using "IDX_pk_Edil_Veneto" on edil_veneto (cost=0.00..8.29 rows=1 width=17)
Index Cond: (edil_veneto.id_domanda = domande.id_domanda)
-> Index Scan using "IDX_2_c_elaout" on c_elaout c_elaout_7 (cost=0.00..14.54 rows=1 width=12)
Index Cond: (c_elaout_7.id_domanda = domande.id_domanda)
Filter: ((c_elaout_7.node)::text = 'contributo_regolare'::text)
-> Seq Scan on r_luoghi (cost=0.00..200.84 rows=10584 width=11)
-> Index Scan using "IDX_2_c_elaout" on c_elaout c_elaout_6 (cost=0.00..14.54 rows=1 width=12)
Index Cond: (c_elaout_6.id_domanda = domande.id_domanda)
Filter: ((c_elaout_6.node)::text = 'contributo_sociale'::text)
-> Seq Scan on ve_edil_tp_superfici (cost=0.00..1.03 rows=3 width=11)
-> Index Scan using "IDX_2_c_elaout" on c_elaout c_elaout_5 (cost=0.00..14.54 rows=1 width=12)
Index Cond: (c_elaout_5.id_domanda = domande.id_domanda)
Filter: ((c_elaout_5.node)::text = 'contributo'::text)
-> Index Scan using "IDX_2_c_elaout" on c_elaout c_elaout_4 (cost=0.00..14.54 rows=1 width=12)
Index Cond: (c_elaout_4.id_domanda = domande.id_domanda)
Filter: ((c_elaout_4.node)::text = 'esito'::text)
-> HashAggregate (cost=31.35..44.24 rows=1031 width=11)
-> Seq Scan on ve_edil_rendite (cost=0.00..25.57 rows=1157 width=11)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
