Re: How to find greatest record before known values fast

Hi!

Thank you.

In my db people often looks for different period sales using different
filters and will sum
There are lot of sales and every sale is individual record in sales table.
So increasing sequential scan speed is important.

I tried

create table t1(v char(100), p numeric(12,5));
create table t2(v varchar(100), p numeric(12,5));
insert into t1 select '', generate_series from generate_series(1,1000000);
insert into t2 select '', generate_series from generate_series(1,1000000);

and after that measured speed of

select sum(p) from t1

and

select sum(p) from t2

Both of them took approximately 800 ms

Also select max(length(v)) from t1 and select max(length(v)) from t2

speed is the same

Also I tested with 1600000 rows production char(100) type column database by
running

select sum(decimalcolumn) from testtable

before and after running

alter table testtable alter char100column type varchar(100)

Select sum command tooks 1700 ms in both cases.

So there is no difference in sequential scan speed.
Replacing char with varchar requires re-writing some parts of code.
Disk space is minor issue compared to cost of code-rewrite.
It looks like it is not reasonable to replace char with varchar.

Andrus