On Tue, 2008-08-12 at 15:46 -0400, Tom Lane wrote:
> Simon Riggs <[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.
>
> This is only going to matter for a table of 1 block (or at least very
> few blocks), and for such a table it's highly likely that it's in RAM
> anyway. So I'm unconvinced that the proposed change represents a
> better model of reality.
The access cost should be the same for a 1 block table, whether its on
disk or in memory.
> Perhaps more to the point, you haven't provided any actual evidence
> that this is a better approach. I'm disinclined to tinker with the
> fundamental cost models on the basis of handwaving.
I've written a simple test suite
psql -f seq.sql -v numblocks=x -v pkval=y -v filler=z
to investigate various costs and elapsed times.
AFAICS the cost cross-over is much higher than the actual elapsed time
cross-over for both narrow and wide tables.
Thats why using SET enable_seqscan=off helps performance in many cases,
or why people reduce random_page_cost to force index selection.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
drop table if exists block:numblocks;
create table block:numblocks (col1 integer not null, col2 text not null);
insert into block:numblocks
select generate_series(1,8192*:numblocks/(48+:filler))
,repeat('a', :filler);
create unique index idx_block:numblocks on block:numblocks (col1);
analyze block:numblocks;
select pg_relation_size('block' || (:numblocks)::text);
\timing
\pset pager off
explain
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
set enable_seqscan = off;
explain
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
select * from block:numblocks where col1 = :pkval;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
