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

From: Matthew Wakeling
Subject: Re: Shouldn't the planner have a higher cost for reverse index scans?
Date: ,
Msg-id: alpine.DEB.2.00.0904141036100.4053@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane)
List: pgsql-performance

Tree view

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 by date:

From: Merlin Moncure
Date:
Subject: Re: Nested query performance issue
From: Stephen Frost
Date:
Subject: Re: INSERT times - same storage space but more fields -> much slower inserts