On Tue, 16 Jun 2009, Alberto Dalmaso wrote:
What does your postgresql.conf file look like?
enable_hashjoin = off enable_nestloop = off enable_seqscan = off enable_sort = off
Why are these switched off?
and that is the explain of the too slow simple query "Merge Join (cost=0.00..1032305.52 rows=4 width=12)" " Merge Cond: (domande.id_dichiarazione = c_elaout.id_domanda)" " -> Index Scan using "IDX_8_domande" on domande (cost=0.00..8.39 rows=1 width=4)" " Index Cond: (id_domanda = 4165757)" " -> Index Scan using "IDX_2_c_elaout" on c_elaout (cost=0.00..1030283.89 rows=805279 width=12)" " Filter: ((c_elaout.node)::text = 'Invalido'::text)" this cost 15 sec with mergejoin to off: "Nested Loop (cost=100000000.00..100000022.97 rows=4 width=12)" " -> Index Scan using "IDX_8_domande" on domande (cost=0.00..8.39 rows=1 width=4)" " Index Cond: (id_domanda = 4165757)" " -> Index Scan using "IDX_2_c_elaout" on c_elaout (cost=0.00..14.54 rows=4 width=12)" " Index Cond: (c_elaout.id_domanda = domande.id_dichiarazione)" " Filter: ((c_elaout.node)::text = 'Invalido'::text)" this cost 15 msec!!!
Well duh. What you're effectively doing is telling Postgres to NEVER use a nested loop. Then you're getting upset because it isn't using a nested loop. When you tell it to NEVER use anything (switching all join algorithms off), it ignores you and chooses the right plan anyway.
Matthew -- You can configure Windows, but don't ask me how. -- Bill Gates -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
