Re: [HACKERS] SeqScan costs

[Decibel!]

On Aug 12, 2008, at 4:52 PM, Andrew Gierth wrote:

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


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)

insert into oneblock select i, 'row ' || i from generate_series (1,200) i;

  from generate_series(1,200) i, generate_series(1,500000) j;

   InitPlan

           Filter: (id = 200)

 Total runtime: 79055.822 ms
(8 rows)


  from generate_series(1,200) i, generate_series(1,500000) j;

   InitPlan

           Filter: (id = 1)

 Total runtime: 87702.315 ms
(8 rows)

[EMAIL PROTECTED] set enable_seqscan =off;
SET

  from generate_series(1,200) i, generate_series(1,500000) j;

   InitPlan

           Index Cond: (id = 200)

 Total runtime: 83024.952 ms
(8 rows)


  from generate_series(1,200) i, generate_series(1,500000) j;

   InitPlan

           Index Cond: (id = 1)

 Total runtime: 82598.556 ms
(8 rows)

[EMAIL PROTECTED]
--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828

Roughly what I get on my MBP (I'm about a factor of 2 slower). This makes me think it's an issue of having to slog through an entire page one row at a time vs just using the index. To test this I tested selecting i=200 (remember we start filling data at the back of the page, so 200 would actually be the front, and I'm assuming the first value that would be hit) vs i=1. With seqscans, I saw about a 10% difference. With index scans the difference was moot, but also note that now index scans are in-between seqscans in performance. [EMAIL PROTECTED] explain analyze select (select value from oneblock where id = 200) QUERY PLAN ------------------------------------------------------------------------ ----------------------------------------------------------------- Nested Loop (cost=17.00..20029.50 rows=1000000 width=0) (actual time=270.867..65821.373 rows=100000000 loops=1) -> Seq Scan on oneblock (cost=0.00..3.50 rows=1 width=7) (actual time=0.052..0.053 rows=1 loops=1) -> Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=0) (actual time=0.062..0.351 rows=200 loops=1) -> Materialize (cost=13.50..23.50 rows=1000 width=0) (actual time=1.368..164.634 rows=500000 loops=200) -> Function Scan on generate_series j (cost=0.00..12.50 rows=1000 width=0) (actual time=270.743..459.335 rows=500000 loops=1) [EMAIL PROTECTED] explain analyze select (select value from oneblock where id = 1) QUERY PLAN ------------------------------------------------------------------------ ----------------------------------------------------------------- Nested Loop (cost=17.00..20029.50 rows=1000000 width=0) (actual time=261.941..72937.226 rows=100000000 loops=1) -> Seq Scan on oneblock (cost=0.00..3.50 rows=1 width=7) (actual time=0.025..0.056 rows=1 loops=1) -> Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=0) (actual time=0.060..0.346 rows=200 loops=1) -> Materialize (cost=13.50..23.50 rows=1000 width=0) (actual time=1.375..182.474 rows=500000 loops=200) -> Function Scan on generate_series j (cost=0.00..12.50 rows=1000 width=0) (actual time=261.815..448.652 rows=500000 loops=1) [EMAIL PROTECTED] explain analyze select (select value from oneblock where id = 200) QUERY PLAN ------------------------------------------------------------------------ ----------------------------------------------------------------- Nested Loop (cost=21.77..20034.27 rows=1000000 width=0) (actual time=262.219..69851.786 rows=100000000 loops=1) -> Index Scan using oneblock_pkey on oneblock (cost=0.00..8.27 rows=1 width=7) (actual time=0.024..0.026 rows=1 loops=1) -> Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=0) (actual time=0.062..0.355 rows=200 loops=1) -> Materialize (cost=13.50..23.50 rows=1000 width=0) (actual time=1.325..174.314 rows=500000 loops=200) -> Function Scan on generate_series j (cost=0.00..12.50 rows=1000 width=0) (actual time=262.119..449.383 rows=500000 loops=1) [EMAIL PROTECTED] explain analyze select (select value from oneblock where id = 1) QUERY PLAN ------------------------------------------------------------------------ ----------------------------------------------------------------- Nested Loop (cost=21.77..20034.27 rows=1000000 width=0) (actual time=262.175..68943.985 rows=100000000 loops=1) -> Index Scan using oneblock_pkey on oneblock (cost=0.00..8.27 rows=1 width=7) (actual time=0.023..0.025 rows=1 loops=1) -> Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=0) (actual time=0.062..0.339 rows=200 loops=1) -> Materialize (cost=13.50..23.50 rows=1000 width=0) (actual time=1.325..176.056 rows=500000 loops=200) -> Function Scan on generate_series j (cost=0.00..12.50 rows=1000 width=0) (actual time=262.079..454.692 rows=500000 loops=1)

smime.p7s
Description: S/MIME cryptographic signature