Re: performance with query

От: Matthew Wakeling
Тема: Re: performance with query
Дата: ,
Msg-id: alpine.DEB.2.00.0906161554550.4337@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: performance with query  (Alberto Dalmaso)
Ответы: Re: performance with query  (Alberto Dalmaso)
Список: pgsql-performance

Скрыть дерево обсуждения

performance with query  (Alberto Dalmaso, )
 Re: performance with query  (Joshua Tolley, )
 Re: performance with query  ("Kevin Grittner", )
 Re: performance with query  (Alberto Dalmaso, )
  Re: performance with query  (Matthew Wakeling, )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  (Tom Lane, )
     Re: performance with query  (Alberto Dalmaso, )
      Re: performance with query  (Tom Lane, )
      Re: performance with query (OT)  ("Albe Laurenz", )
    Re: performance with query  ("Kevin Grittner", )
  Re: performance with query  ("Kevin Grittner", )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  ("Kevin Grittner", )
    Re: performance with query  ("Kevin Grittner", )
     Re: performance with query  (Alberto Dalmaso, )
      Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  (Anthony Presley, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  ("Albe Laurenz", )
        Re: Speeding up a query.  (Grzegorz Jaśkiewicz, )
       Re: Speeding up a query.  (Alberto Dalmaso, )
        Re: Speeding up a query.  ("Kevin Grittner", )
        Re: Speeding up a query.  (Tom Lane, )
       Re: Speeding up a query.  (Merlin Moncure, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  (Simon Riggs, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
 Re: performance with query  (Alberto Dalmaso, )
 Re: performance with query  (Alberto Dalmaso, )
  Re: performance with query  ("Kevin Grittner", )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  ("Kevin Grittner", )
     Re: performance with query  (Alberto Dalmaso, )
      Re: performance with query  ("Kevin Grittner", )
       Re: performance with query  (Tom Lane, )
        Re: performance with query  ("Kevin Grittner", )

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


В списке pgsql-performance по дате сообщения:

От: "Mark Steben"
Дата:
Сообщение: Performance discrepancy
От: Aaron Turner
Дата:
Сообщение: High cost of ... where ... not in (select ...)