Re: Shouldn't the planner have a higher cost for reverse index scans?

От: Matthew Wakeling
Тема: Re: Shouldn't the planner have a higher cost for reverse index scans?
Дата: ,
Msg-id: alpine.DEB.2.00.0904141036100.4053@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane)
Список: pgsql-performance

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

Shouldn't the planner have a higher cost for reverse index scans?  (Josh Berkus, )
 Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
  Re: Shouldn't the planner have a higher cost for reverse index scans?  (Josh Berkus, )
   Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
    Re: Shouldn't the planner have a higher cost for reverse index scans?  (Josh Berkus, )
     Re: Shouldn't the planner have a higher cost for reverse index scans?  (Lists, )
      Re: Shouldn't the planner have a higher cost for reverse index scans?  (Grzegorz Jaśkiewicz, )
      Re: Shouldn't the planner have a higher cost for reverse index scans?  (Merlin Moncure, )
       Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
      Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
       Re: Shouldn't the planner have a higher cost for reverse index scans?  (Lists, )
        Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
    Re: Shouldn't the planner have a higher cost for reverse index scans?  (Matthew Wakeling, )

On Fri, 10 Apr 2009, Tom Lane wrote:
> Josh Berkus <> writes:
>> Not as far as I can tell.   It looks to me like the planner is assuming
>> that a forwards index scan and a reverse index scan will have the same
>> cost.
>
> Right, because they do.  If you think otherwise, demonstrate it.

They do when the correlation of indexed value versus position in the table
is low, resulting in random access. However, when the correlation is near
1, then the index scan approximates to sequential access to disc. In that
case, scan direction would be important.

Of course, there's the separate issue that correlation isn't actually that
good a measure of the cost of an index scan, but I'm not sure what is
better, and feasible.

Matthew


--
 Our riverbanks and seashores have a beauty all can share, provided
 there's at least one boot, three treadless tyres, a half-eaten pork
 pie, some oil drums, an old felt hat, a lorry-load of tar blocks,
 and a broken bedstead there.                   -- Flanders and Swann


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

От: Merlin Moncure
Дата:
Сообщение: Re: Nested query performance issue
От: Stephen Frost
Дата:
Сообщение: Re: INSERT times - same storage space but more fields -> much slower inserts