Re: performance with query

От: Tom Lane
Тема: Re: performance with query
Дата: ,
Msg-id: 16299.1245168776@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: 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", )

Alberto Dalmaso <> writes:
> Ok, but the problem is that my very long query performes quite well when
> it works with merge join but it cannot arrive to an end if it use other
> kind of joining.
> If i put all the parameter to on, as both of you tell me, in the
> explanation I'll see that the db use nasted loop.
> If i put to off nasted loop, it will use hash join.
> How can I write the query so that the analyzer will use mergejoin (that
> is the only option that permit the query to give me the waited answare)
> without changing the settings every time on the connection?

You have the wrong mindset completely.  Instead of thinking "how can I
force the planner to do it my way", you need to be thinking "why is the
planner guessing wrong about which is the best way to do it?  And how
can I improve its guess?"

There's not really enough information in what you've posted so far to
let people help you with that question, but one thing that strikes me
from the EXPLAIN is that you have a remarkably large number of joins.
Perhaps increasing from_collapse_limit and/or join_collapse_limit
(to more than the number of tables in the query) would help.

            regards, tom lane


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

От: Alvaro Herrera
Дата:
Сообщение: Re: High cost of ... where ... not in (select ...)
От: Aaron Turner
Дата:
Сообщение: Re: High cost of ... where ... not in (select ...)