Re: SeqScan costs

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> Proposal: Make the first block of a seq scan cost
>> random_page_cost, then after that every additional block costs
>> seq_page_cost.

Tom> This is only going to matter for a table of 1 block (or at least
Tom> very few blocks), and for such a table it's highly likely that
Tom> it's in RAM anyway. So I'm unconvinced that the proposed change
Tom> represents a better model of reality.

Simple example which demonstrates a 10x speed improvement for index
scan over seqscan for a 1-block table (on 8.3.3):

create table oneblock (id integer primary key, value text not null);
insert into oneblock select i, 'row ' || i from generate_series(1,200) i;

test=> select pg_relation_size('oneblock');
pg_relation_size
------------------
8192

analyze oneblock;

set enable_seqscan=true;

select (select value from oneblock where id = i)
from generate_series(1,200) i, generate_series(1,5000) j;
Time: 25596.709 ms (that's 25.6 us per row)

set enable_seqscan=false;

select (select value from oneblock where id = i)
from generate_series(1,200) i, generate_series(1,5000) j;
Time: 2415.691 ms (that's 2.4 us per row)

(removing the subselect entirely gives 0.4us per row, so it's actually
about a 12x speed difference for the subselect alone.)

The planner costs the seqscan at 3.50 and the indexscan at 8.27.

--
Andrew (irc:RhodiumToad)