Re: performance with query

От: Joshua Tolley
Тема: Re: performance with query
Дата: ,
Msg-id: 20090616141322.GB23544@eddie
(см: обсуждение, исходный текст)
Ответ на: 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, Jun 16, 2009 at 03:37:42PM +0200, Alberto Dalmaso wrote:
> Hi everybody, I'm creating my database on postgres and after some days
> of hard work I'm arrived to obtain good performance and owfull
> performace with the same configuration.
> I have complex query that perform very well with mergejoin on and
> nestloop off.
> If I activate nestloop postgres try to use it and the query execution
> become inconclusive: after 3 hours still no answare so I kill the query.
> Tht's ok but, with this configuration, very simple and little query like
> "slect colum from table where primarykey=value bacome incredibly slow.
> The only solutionI found at the momento is to set mergejoin to off
> before doing this query.
> That is an awfull solution because with that solution I have to change
> all the software (a big, old software) in the (many) points in witch
> this kind of query are used (the same problem to set to off mergejoin
> for all the system and activate it on che connection that have to make
> the hard query).
> Do you have any suggestion to accelerate both complex and silply query?
> I've tried a lot of configuration in enabling different  "Planner Method
> Configuration" but the only combination that really accelerate hard
> query is mergejoin on and nestloop off, other settings seems to be
> useless.
> Thank's in advance.

It would be helpful if you posted EXPLAIN ANALYZE results for both queries.
This will require you to run each query to completion; if that's not possible
for the 3 hour query, at least run EXPLAIN and post those results.

- Josh / eggyknap

Вложения

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

От: Anthony Presley
Дата:
Сообщение: Re: Speeding up a query.
От: "Mark Steben"
Дата:
Сообщение: Performance discrepancy