Re: performance with query

От: Kevin Grittner
Тема: Re: performance with query
Дата: ,
Msg-id: 4A3A4EB20200002500027DAE@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: performance with query  (Alberto Dalmaso)
Ответы: Re: performance with query  (Tom Lane)
Список: 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 <> wrote:
> P.S.: to understand what the query has to make (and 80% of the view
> hve these to make): a lot of time is spend to pivoting a table with
> a structure like
> identifier, description_of_value, numeric value
> that has to be transformed in
> identifier, description_1, description_2, ..., description_n
> where n is not a fixed number (it changes in function of the type of
> calculation that was used to generate the rows in the table).
>
> perhaps this information could help.

What would help more is the actual query, if that can be shared.  It
leaves a lot less to the imagination than descriptions of it.

There are a couple things which have been requested which would help
pin down the reason the optimizer is not getting to a good plan, so
that it can be allowed to do a good job.  As Tom said, this would be a
much more productive focus than casting about for ways to force it to
do what you think is the best thing.  (Maybe, given the chance, it can
come up with a plan which runs in seconds, rather than over the 24
minutes you've gotten.)

With all the optimizer options on, and the from_collapse_limit and
join_collapse_limit values both set to 100, run an EXPLAIN (no
ANALYZE) on your big problem query.  Let us know how long the EXPLAIN
runs.  If it gets any errors, copy and paste all available
information.  (General descriptions aren't likely to get us very far.)
Since EXPLAIN without ANALYZE only *plans* the query, but doesn't run
it, it should not take long to do this.

If there are any views or custom functions involved, showing those
along with the query source would be good.

If we get this information, we have a much better chance to find the
real problem and get it fixed.

-Kevin


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

От: "Kevin Grittner"
Дата:
Сообщение: Re: select max() much slower than select min()
От: Kenneth Marshall
Дата:
Сообщение: Re: 8.4 COPY performance regression on Solaris