From: | Matthew Wakeling <matthew(at)flymine(dot)org> |
---|---|
To: | Alberto Dalmaso <dalmaso(at)clesius(dot)it> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: performance with query |
Date: | 2009-06-16 14:58:56 |
Message-ID: | alpine.DEB.2.00.0906161554550.4337@aragorn.flymine.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2009-06-16 15:06:33 | Re: performance with query |
Previous Message | Alberto Dalmaso | 2009-06-16 14:45:53 | Re: performance with query |