>>>>> "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)