Intra-page indexing benchmark

g1.sh

#!/usr/bin/env bash

set -e

declare -a blksz=("2048" "4096" "8192" "16384" "32768")
declare -a trshs=("8" "16" "24" "32")

## now loop through the above array
for size in "${blksz[@]}"
do
   echo "block size $size"
   for trs in "${trshs[@]}"
    do
        echo "results/S${size}T${trs}"
        cd ~/project/pgsql
        git checkout .
        git checkout master
        sed -i -- "s/define BLCKSZ.*/define BLCKSZ ${size}/" src/include/pg_config.h
        sed -i -- "s/define GIST_SKIPGROUP_THRESHOLD.*/define GIST_SKIPGROUP_THRESHOLD	${trs}/" src/include/access/gist_private.h

        pkill -9 postgres || true
        make -j 16 && make install

        rm -rf ~/DemoDb
        cd ~/project/bin/
        ./initdb ~/DemoDb
        echo "shared_buffers = 2GB">>~/DemoDb/postgresql.conf
        ./pg_ctl -D ~/DemoDb start
        #./psql postgres -c "create extension cube;"


        ./psql postgres<../pgsql/g1.sql| grep Time >> ~/project/bin/result/S${size}T${trs}M

        cd ~/project/pgsql
        git checkout .
        git checkout g3
        sed -i -- "s/define BLCKSZ.*/define BLCKSZ ${size}/" src/include/pg_config.h
        sed -i -- "s/define GIST_SKIPGROUP_THRESHOLD.*/define GIST_SKIPGROUP_THRESHOLD	${trs}/" src/include/access/gist_private.h

        pkill -9 postgres || true
        make -j 16 && make install

        rm -rf ~/DemoDb
        cd ~/project/bin/
        ./initdb ~/DemoDb
        echo "shared_buffers = 2GB">>~/DemoDb/postgresql.conf
        ./pg_ctl -D ~/DemoDb start
        #./psql postgres -c "create extension cube;"


        ./psql postgres<../pgsql/g1.sql| grep Time >> ~/project/bin/result/S${size}T${trs}G3
    done
done
exit



#./pg_ctl -D ~/DemoDb stop

g1.sql

\timing
--\o null.txt
create unlogged table x(c point); 
create index on x using gist(c); 
select 'Time for random insert';
insert into x select point(random(),random()) c from generate_series(1,10000000) y; 
--insert into x select point(x/1000.0,y/1000.0) c from generate_series(1,1000) y,generate_series(1,100) x;

--delete from x;
vacuum;

set enable_bitmapscan = off;
select 'Time for random select';
explain analyze
with pts as (select random() x,random() y from generate_series(1,100000) y),
queries as (select box(point(x,y),point(x+0.01,y+0.01)) b from pts)
select (select count(*) from x where x.c<@q.b) from queries q;

--explain analyze
--with pts as (select random() x,random() y from generate_series(1,100000) y),
--queries as (select point(x,y) b from pts)
--select (select * from x order by x.c<->q.b limit 1) from queries q;

drop table x;

create unlogged table x(c point); 
create index on x using gist(c); 

select 'Time for ordered insert';
insert into x select point(x/1000.0,y/10000.0) c from generate_series(1,1000) y,generate_series(1,10000) x;

--delete from x;
vacuum;

set enable_bitmapscan = off;
select 'Time for ordered select';
explain analyze
with pts as (select random() x,random() y from generate_series(1,100000) y),
queries as (select box(point(x,y),point(x+0.01,y+0.01)) b from pts)
select (select count(*) from x where x.c<@q.b) from queries q;

--explain analyze
--with pts as (select random() x,random() y from generate_series(1,100000) y),
--queries as (select point(x,y) b from pts)
--select (select * from x order by x.c<->q.b limit 1) from queries q;

drop table x;