>>>>> "Tom" == Tom Lane <[EMAIL PROTECTED]> 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)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
