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?
Дата: ,
Msg-id: 21658.1239371437@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: 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?  (Josh Berkus)
Список: 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, )

Josh Berkus <> writes:
> Now, what that says to me is that for this system reverse sequential
> reads are 1/4 the speed of forwards reads.  And from my testing
> elsewhere, that seems fairly typical of disk systems in general.

Well, that's because filesystems try to lay out files so that logically
successive sectors are about as far apart as needed to support the
disk's maximum transfer rate.  If you fetch them in reverse order,
then instead of optimizing the rotational latency you find you are
pessimizing it.  This has got approximately nothing to do with
indexscans, either forward or reverse, because then we aren't fetching
blocks in a pre-optimized order.

> Now, while index scans (for indexes on disk) aren't 100% sequential
> reads, it seems like we should be increasing (substantially) the
> estimated cost of reverse index scans if the index is likely to be on
> disk.  No?

AFAICS this is already folded into random_page_cost.

            regards, tom lane


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

От: Greg Smith
Дата:
Сообщение: Re: Using IOZone to simulate DB access patterns
От: "Kevin Grittner"
Дата:
Сообщение: Re: determining the locks that will be held by a query