Re: slow query performance

От: Kenneth Marshall
Тема: Re: slow query performance
Дата: ,
Msg-id: 20100611132817.GQ3063@aart.is.rice.edu
(см: обсуждение, исходный текст)
Ответ на: Re: slow query performance  (Anj Adu)
Ответы: Re: slow query performance  (Matthew Wakeling)
Список: pgsql-performance

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

slow query performance  (Anj Adu, )
 Re: slow query performance  (Andy Colson, )
  Re: slow query performance  (Anj Adu, )
   Re: slow query performance  (Robert Haas, )
    Re: slow query performance  (Tom Lane, )
     Re: slow query performance  (Anj Adu, )
      Re: slow query performance  (Robert Haas, )
       Re: slow query performance  (Anj Adu, )
        Re: slow query performance  (Robert Haas, )
         Re: slow query performance  (Anj Adu, )
          Re: slow query performance  (Robert Haas, )
        Re: slow query performance  (Anj Adu, )
         Re: slow query performance  (Kenneth Marshall, )
          Re: slow query performance  (Anj Adu, )
           Re: slow query performance  (Kenneth Marshall, )
            Re: slow query performance  (Matthew Wakeling, )

If you check the archives, you will see that this is not easy
to do because of the effects of caching. The default values
were actually chosen to be a good compromise between fully
cached in RAM and totally un-cached. The actual best value
depends on the size of your database, the size of its working
set, your I/O system and your memory. The best recommendation
is usually to use the default values unless you know something
about your system that moves it out of that arena.

Regards,
Ken

On Fri, Jun 11, 2010 at 06:23:31AM -0700, Anj Adu wrote:
> Is there a way to determine a reasonable value for random_page_cost
> via some testing with OS commands. We have several postgres databases
> and determining this value on a case by case basis may not be viable
> (we may have to go with the defaults)
>
> On Fri, Jun 11, 2010 at 5:44 AM, Kenneth Marshall <> wrote:
> > Hi Anj,
> >
> > That is an indication that your system was less correctly
> > modeled with a random_page_cost=2 which means that the system
> > will assume that random I/O is cheaper than it is and will
> > choose plans based on that model. If this is not the case,
> > the plan chosen will almost certainly be slower for any
> > non-trivial query. You can put a 200mph speedometer in a
> > VW bug but it will never go 200mph.
> >
> > Regards,
> > Ken
> >
> > On Thu, Jun 10, 2010 at 07:54:01PM -0700, Anj Adu wrote:
> >> I changed random_page_cost=4 (earlier 2) and the performance issue is gone
> >>
> >> I am not clear why a page_cost of 2 on really fast disks would perform badly.
> >>
> >> Thank you for all your help and time.
> >>
> >> On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu <> wrote:
> >> > Attached
> >> >
> >> > Thank you
> >> >
> >> >
> >> > On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <> wrote:
> >> >> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <> wrote:
> >> >>> The plan is unaltered . There is a separate index on theDate as well
> >> >>> as one on node_id
> >> >>>
> >> >>> I have not specifically disabled sequential scans.
> >> >>
> >> >> Please do "SHOW ALL" and attach the results as a text file.
> >> >>
> >> >>> This query performs much better on 8.1.9 on a similar sized
> >> >>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
> >> >>
> >> >> Well that could certainly matter...
> >> >>
> >> >> --
> >> >> Robert Haas
> >> >> EnterpriseDB: http://www.enterprisedb.com
> >> >> The Enterprise Postgres Company
> >> >>
> >> >
> >>
> >> --
> >> Sent via pgsql-performance mailing list ()
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-performance
> >>
> >
>


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

От: Tim Landscheidt
Дата:
Сообщение: Re: Analysis Function
От: Tom Lane
Дата:
Сообщение: Re: Analysis Function