test.sh

application/x-shellscript

Download

#!/bin/sh

t=tmm
power=7

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

if [[ 1 -eq 0 || $table_count -eq 0 ]]; then

echo "
  drop table if exists $t ;
  create table $t as
    select i,
         cast(random() *  10^${power} as integer) as r
--  from generate_series(1,      10000 ) as f(i) ;
--  from generate_series(1,    1000000 ) as f(i) ;
--  from generate_series(1,    5000000 ) as f(i) ;
    from generate_series(1,   50000000 ) as f(i) ;
    analyze $t;
--  table $t limit 5;
    create index ${t}_minmax_idx on $t using minmax (r);
    analyze $t;
" | psql -Xa
# else
#   echo "table_count [$table_count] ( $t )"
fi

diff2=100
diff3=1000
diff4=10000

  i1=$( echo "select cast(random() * 10^${power} as integer) " | psql -qtAX );
# i2=$( echo "select cast(random() * 10^${power} as integer) " | psql -qtAX );
  i2=$(( $i1 + $diff2 ));
  i3=$(( $i1 + $diff3 ));
  i4=$(( $i1 + $diff4 ));

echo $i1
echo " + $diff2"
echo " + $diff3"
echo " + $diff4"

# echo -ne "$i1\n$i2\n$i3\n" | perl -MTie::Comma -ne 'print $comma{ $_ }'

( 
  echo "set enable_bitmapscan = 1; explain analyze select * from $t where r between           $i1 and $i2;  --  + $diff2"; 
  echo "set enable_bitmapscan = 0; explain analyze select * from $t where r between           $i1 and $i2;  --  + $diff2"; 
# echo "set enable_bitmapscan = 1; explain analyze select * from $t where r between symmetric $i1 and $i2;  --  + $diff2"; 
# echo "set enable_bitmapscan = 0; explain analyze select * from $t where r between symmetric $i1 and $i2;  --  + $diff2"; 
  echo "set enable_bitmapscan = 1; explain analyze select * from $t where r between           $i1 and $i3;  --  + $diff3"; 
  echo "set enable_bitmapscan = 0; explain analyze select * from $t where r between           $i1 and $i3;  --  + $diff3"; 
  echo "set enable_bitmapscan = 1; explain analyze select * from $t where r between           $i1 and $i4;  --  + $diff4";
  echo "set enable_bitmapscan = 0; explain analyze select * from $t where r between           $i1 and $i4;  --  + $diff4"; 
) | psql -qaX