Re: performance with query

От: Kevin Grittner
Тема: Re: performance with query
Дата: ,
Msg-id: 4A37640E0200002500027BCF@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: 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 <> wrote:

> 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.

We'll need  a lot more information to be able to provide useful
advice.

What version of PostgreSQL?

What OS?

What does the hardware look like?  (CPUs, drives, memory, etc.)

Do you have autovacuum running?  What other regular maintenance to you
do?

What does your postgresql.conf file look like?  (If you can strip out
all comments and show the rest, that would be great.)

With that as background, if you can show us the schema for the
table(s) involved and the text of a query, along with the EXPLAIN
ANALYZE output (or just EXPLAIN, if the query runs too long to get the
EXPLAIN ANALYZE results) that would allow us to wee where things are
going wrong.  Please show this information without setting any of the
optimizer options off; but then, as a diagnostic step, *also* show
EXPLAIN ANALYZE results when you set options to a configuration that
runs faster.

-Kevin


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

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