Re: Help Me Understand Why I'm Getting a Bad Query Plan

Список
Период
Сортировка
От Tom Lane
Тема Re: Help Me Understand Why I'm Getting a Bad Query Plan
Дата
Msg-id 11650.1238033714@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy)
Ответы Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy)
Список pgsql-performance
Дерево обсуждения
Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy, )
 Re: Help Me Understand Why I'm Getting a Bad Query Plan  (marcin mank, )
  Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy, )
   Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Josh Berkus, )
    Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy, )
     Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Tom Lane, )
      Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy, )
       Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Tom Lane, )
        Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy, )
Bryan Murphy <> writes:
> I tried that already, but I decided to try again in case I messed up
> something last time.  Here's what I ran.  As you can see, it still
> chooses to do a sequential scan.  Am I changing the stats for those
> columns correctly?

I think what you should be doing is messing with the cost parameters
... and not in the direction you tried before.  I gather from
    effective_cache_size = 12GB
that you have plenty of RAM on this machine.  If the tables involved
are less than 1GB then it's likely that you are operating in a fully
cached condition, and the default cost parameters are not set up for
that.  You want to be charging a lot less for page accesses relative to
CPU effort.  Try reducing both seq_page_cost and random_page_cost to 0.5
or even 0.1.  You'll need to watch your other queries to make sure
nothing gets radically worse though ...

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Mark Kirkwood
Дата:
Сообщение: Re: Raid 10 chunksize
Следующее
От: Dave Cramer
Дата:
Сообщение: I have a fusion IO drive available for testing