casanova_test.sh

#!/bin/sh

#     size_string are perl-readable numberstrings which will become the rowcount (passed to generate_series)
  for size_string in 10_000 100_000 1_000_000 100_000_000 1_000_000_000;
# for size_string in        100_000;
do

prefixed_number=$( printf "%16s" $size_string | perl -ne 's{ }{_}g; print $_;' );

outfile=t_${prefixed_number}__$(date +"%Y%m%d_%H%M").out

echo "-- creating out file $outfile"

> $outfile

setdb.sh >> $outfile

echo "-- rowcount (size_string):  $size_string" >> $outfile
rowcount=$( perl -e 'print '${size_string}'+0' ); # heh, using perl for improved readbility

# echo "rowcount [$rowcount]"

t=t_${size_string}
table_count=$( echo "select count(*) from information_schema.tables where table_schema = 'public' and table_name = '$t'" | psql -qtAX );

if [[ 1 -eq 1 || $table_count -eq 0 ]];
then
        echo "--"                             >> $outfile
        echo "--   rowcount  [$size_string]"  >> $outfile
        echo "--"                             >> $outfile
        echo "        drop table if exists $t;
   /*  
        create table $t (i int);
        create index ${t}_i_minmax_idx on $t using minmax(i); select pg_size_pretty(pg_relation_size('${t}_i_minmax_idx'));
    --  create index ${t}_i_btree_idx  on $t using btree (i); select pg_size_pretty(pg_relation_size('${t}_i_btree_idx' ));
        insert into $t select generate_series(1, $rowcount);
        analyze $t;
   */
        create table $t as select cast(i as integer) from generate_series(1, $rowcount) as f(i) ;
        create index ${t}_i_minmax_idx on $t using minmax(i); select pg_size_pretty(pg_relation_size('${t}_i_minmax_idx'));
    --  create index ${t}_i_btree_idx  on $t using btree (i); select pg_size_pretty(pg_relation_size('${t}_i_btree_idx' ));
        analyze $t;        
        " | psql -qa >> $outfile
fi

( echo "create index ${t}_i_btree_idx  on $t using btree (i); ";
  echo "analyze $t;        " )| psql -qX >> $outfile

x_start=10000
if [[ "$1" -gt 0 ]]; then x_start=$( echo "select cast(random() * ${rowcount} as integer)" | psql -qtAX ); fi
x_end=$(( $x_start + 99 ))

(
  echo "-- x_start [$x_start]     x_end [$x_end]"
  echo "\\dti+ ${t}*"
  echo "                   select pg_relation_size('${t}'             ) as  table_size, 'size table'::text as what, pg_size_pretty(pg_relation_size('${t}'             )) as  table_size
        union all  select pg_relation_size('${t}_i_btree_idx' ) as  btree_size, 'size btree index' , pg_size_pretty(pg_relation_size('${t}_i_btree_idx' ))
        union all  select pg_relation_size('${t}_i_minmax_idx') as minmax_size, 'size minmax index', pg_size_pretty(pg_relation_size('${t}_i_minmax_idx')); ";
  echo " ";
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 1; set enable_indexscan = 1; explain analyze select i from $t where i between $x_start and $x_end;";
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 1; set enable_indexscan = 1; explain analyze select i from $t where i between $x_start and $x_end;";
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 0; set enable_indexscan = 0; explain analyze select i from $t where i between $x_start and $x_end;";
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 0; set enable_indexscan = 0; explain analyze select i from $t where i between $x_start and $x_end;";
  echo "drop index if exists ${t}_i_btree_idx;"
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 1; set enable_indexscan = 1; explain analyze select i from $t where i between $x_start and $x_end;";
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 1; set enable_indexscan = 1; explain analyze select i from $t where i between $x_start and $x_end;";
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 0; set enable_indexscan = 0; explain analyze select i from $t where i between $x_start and $x_end;";
  echo "set enable_bitmapscan = 1; set enable_indexonlyscan = 0; set enable_indexscan = 0; explain analyze select i from $t where i between $x_start and $x_end;";
# echo "-- runtime enable_bitmapscan = 0, enable_indexonlyscan = 0 :"
  echo "set enable_bitmapscan = 0; set enable_indexonlyscan = 0; set enable_indexscan = 0; explain analyze select i from $t where i between $x_start and $x_end;";
# echo "set enable_bitmapscan = 0; set enable_indexonlyscan = 0; set enable_indexscan = 0; explain analyze select i from $t where i between $x_start and $x_end;";
) | psql -qatX >> $outfile

ls -lh $outfile >> $outfile

done

echo "-- total .out list:"
ls -lh t*.out;