Re: SeqScan costs

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.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 leastTom> very few blocks), and for such a table it's
highlylikely thatTom> it's in RAM anyway.  So I'm unconvinced that the proposed changeTom> 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)