Re: Speeding up a query.

От: Kevin Grittner
Тема: Re: Speeding up a query.
Дата: ,
Msg-id: 4A38BD540200002500027C85@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Speeding up a query.  (Alberto Dalmaso)
Список: pgsql-performance

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

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", )

Alberto Dalmaso <> wrote:

> P.S.: i'm trying with all enable_* to on and pumping to higher
> values from_collapse_limit and join_collapse_limit that I've put to
> 30.

Tom suggested that you set those numbers higher than the number of
tables joined in the query.  I don't think 30 will do that.

> The result is that the query, after an hour of work, goes out of
> memory (SQL State 53200)...

Ouch!  Can you provide more details?  All information from the
PostgreSQL log about that event would be good.  If there's anything
which might be related in the OS logs from around that time, please
include that, too.

Also, with those settings at a high value, try running just an EXPLAIN
(no ANALYZE) of the query, to see how long that takes, and whether you
have a memory issue during the planning phase.  (You can use \timing
in psql to get a report of the run time of the EXPLAIN.)

-Kevin


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

От: Brian Cox
Дата:
Сообщение: very slow selects on a small table
От: Bryce Ewing
Дата:
Сообщение: Re: Index Scan taking long time