#!/usr/bin/perl
use strict;

use Getopt::Std;


rand($$^time());

my %opt;
getopts('c:q:n:m:h', \%opt);

if ( $opt{h} ) {
        print "Usage:\n$0 [ -c COUNT ] [ -q QUERIESNUM ] [ -n AVERAGEPERQ ] \n";
	print "-c COUNT        - number of record\n";
	print "-q QUERIESNUM   - number of queries\n";
	print "-n AVERAGEPERQ  - desired number of record returning by one query\n";
	print "-m MEMINMEGABYTES - memory for dd in megabytes, default no\n";
        exit(0);
}

my ($count, $name) = ( 1000000, 'pgs_test' );
$count = $opt{c} if defined $opt{c};
my ($qn, $perq) = (500, 100);
$qn = $opt{q} if defined $opt{q};
$perq = $opt{n} if defined $opt{n};
my $mem = 0;
$mem = $opt{m} if defined $opt{m};

if ( $perq*5 > $count ) {
	print "Strage option: too big AVERAGEPERQ\n";
	exit(1);
}


#generate tables
print<<EOT;
drop table ${name}_rnd;
drop table ${name}_clt;
CREATE TABLE ${name}_rnd (
  pos     spoint NOT NULL
);
COPY ${name}_rnd FROM STDIN;
EOT
foreach (1..$count) {
	print '('.(360.0*rand()).'d, '.(180.0*rand() - 90.0)."d)\n";
}

print<<EOT;
\\.

select * into ${name}_clt from ${name}_rnd;
vacuum full analyze;

--create index
create index rnd_idx on ${name}_rnd using gist(pos);

--cluster 
create index bclt_idx on ${name}_clt (pos);
cluster bclt_idx on ${name}_clt;
create index clt_idx on ${name}_clt using gist(pos);
drop index bclt_idx;

vacuum full analyze;
select pg_stat_reset();
 
EOT

#generate queries
my $density=$count / (360.0*180.0);
my $square = $perq / $density;
my $delta = sqrt($square);
my @queries;
for(my $i=0; $i<$qn; $i++) {
	my ($a,$d) = ( 360.0*rand(), 180.0*rand() - 90.0 );
	if ( $a + $delta < 360.0 && $d + $delta < 90.0 ) {
		push @queries, "((${a}d,${d}d),(".($a + $delta).'d,'.($d + $delta).'d))';
	} else {
		$i--;
	}
}
print "\\timing\n";
printtst('Randomized table', 'rnd');
printtst('Clustered table', 'clt');

print "\\! rm -rf tstcrlmem.tmp\n" if $mem>0; 

sub printtst {
	my ($nt, $type) = @_;
	print "\\echo == $nt [$type]==\n";
	print "\\! rm -rf tstcrlmem.tmp\n" if $mem>0; 
	print "\\! dd if=/dev/zero of=tstcrlmem.tmp count=".($mem*1024*1024/512)."\n" if $mem>0; 
	print "\\! date +%s\n";
	foreach my $q ( @queries ) {
		print "SELECT count(*) FROM ${name}_${type} WHERE pos @ sbox '$q';\n";
	}
	print "\\! date +%s\n";
	print "select a from gist_print('${type}_idx') as t(level int, a spherekey) where  level=1;\n";
	print "SELECT sum(relpages) FROM pg_class WHERE relkind='r' AND relname = '${name}_${type}';\n";
	print "SELECT sum(relpages) FROM pg_class WHERE relkind='i' AND relname = '${type}_idx';\n";
	print "select * from pg_statio_user_tables  where relname = '${name}_${type}';\n";
	print "select * from pg_statio_user_indexes where relname = '${name}_${type}';\n";
}