gistbloat.sh

application/x-sh

Download

#!/bin/bash

PATH=~/pgsql.master/bin:$PATH
export PATH

psql postgres <<EOF
create table if not exists geotest_summary (testname text, initsize bigint, finalsize bigint, idx_blks_read bigint, idx_blks_hit bigint);
EOF

psql postgres <<EOF
drop table if exists geotest;
drop table if exists geotest_stats;
EOF

psql postgres <<EOF

-- 1) Create test table with 1000000 points from geonames

create table geotest (id serial, point point);
insert into geotest (point) (select * from geonames order by random() limit 1000000);


-- 2) Create gist index and statistics table. Insert initial statistics (rows count, index size) into table.

create index geotest_idx on geotest using gist (point) with (buffering = off);
create table geotest_stats (id serial, count bigint, size bigint);

insert into geotest_stats (count, size) values ((select count(*) from geotest), pg_relation_size('geotest_idx'));

select pg_stat_reset();
EOF

for ((n=1; n <= 50; n=n+1))
do
  psql postgres <<EOF
-- 3) Delete 10% of geotest and vacuum it.

delete from geotest where id in (select id from geotest order by random() limit 100000);
vacuum geotest;


-- 4) Insert new 100000 points from geonames.

insert into geotest (point) (select * from geonames order by random() limit 100000);

-- 5) Insert current statistics (rows count, index size) into table.
insert into geotest_stats (count, size) values ((select count(*) from geotest), pg_relation_size('geotest_idx'));

-- 6) Repeat 3-5 steps 50 times.

EOF
done

psql postgres <<EOF
insert into geotest_summary (testname, initsize, finalsize, idx_blks_read, idx_blks_hit)
select '$1',
       (select size from geotest_stats where id = 1),
       (select size from geotest_stats where id = 51),
       (select idx_blks_read from pg_statio_user_indexes where indexrelname = 'geotest_idx'),
       (select idx_blks_hit from pg_statio_user_indexes where indexrelname = 'geotest_idx')
EOF