From 1db943b3ca5a8f405155c904964272ce52d96f00 Mon Sep 17 00:00:00 2001
From: "Marc G. Fournier" <scrappy@hub.org>
Date: Fri, 12 Jan 2001 00:16:26 +0000
Subject: commit Oleg and Teodor's RD-tree implementation ... this provides the
 regression tests for the GiST changes ... this should be integrated into the
 regular regression tests similar to Vadim's SPI contrib stuff ...

---
 contrib/intarray/bench/bench.pl       | 104 ++++++++++++++++++++++++++++++++++
 contrib/intarray/bench/create_test.pl |  73 ++++++++++++++++++++++++
 2 files changed, 177 insertions(+)
 create mode 100755 contrib/intarray/bench/bench.pl
 create mode 100755 contrib/intarray/bench/create_test.pl

(limited to 'contrib/intarray/bench')

diff --git a/contrib/intarray/bench/bench.pl b/contrib/intarray/bench/bench.pl
new file mode 100755
index 0000000000..2e30956721
--- /dev/null
+++ b/contrib/intarray/bench/bench.pl
@@ -0,0 +1,104 @@
+#!/usr/bin/perl 
+
+use strict;
+# make sure we are in a sane environment.
+use DBI();
+use DBD::Pg();
+use Time::HiRes qw( usleep ualarm gettimeofday tv_interval );
+use Getopt::Std;
+
+my %opt;
+getopts('d:b:s:veorauc', \%opt);
+
+if ( !( scalar %opt && defined $opt{s} ) ) {
+	print <<EOT;
+Usage:
+$0 -d DATABASE -s SECTIONS [-b NUMBER] [-v] [-e] [-o] [-r] [-a] [-u]
+-d DATABASE   	-DATABASE
+-b NUMBER   	-number of repeats
+-s SECTIONS 	-sections, format	sid1[,sid2[,sid3[...]]]]
+-v 		-verbose (show SQL)
+-e		-show explain
+-r		-use RD-tree index
+-a		-AND section
+-o		-show output
+-u		-unique
+-c 		-count
+
+EOT
+	exit;
+}
+
+$opt{d} ||= '_int4';
+my $dbi=DBI->connect('DBI:Pg:dbname='.$opt{d});
+
+my %table;
+my @where;
+
+$table{message}=1;
+
+if ( $opt{a} ) {
+	if ( $opt{r} ) {
+		push @where, "message.sections @ '{$opt{s}}'";
+	} else {
+		foreach my $sid ( split(/[,\s]+/, $opt{s} )) {
+			push @where, "EXISTS ( select  message_section_map.mid from message_section_map where message.mid=message_section_map.mid and message_section_map.sid = $sid )";
+		}
+	}
+} else {
+	if ( $opt{r} ) {
+		push @where, "message.sections && '{$opt{s}}'";
+	} else {
+		$table{message_section_map} = 1;
+		push @where, "message.mid = message_section_map.mid";
+		push @where, "message_section_map.sid in ($opt{s})";
+	}
+}
+
+my $outf;
+if ( $opt{c} ) {
+	$outf = ( $opt{u} ) ? 'count( distinct message.mid )' : 'count( message.mid )';
+} else {
+	$outf = ( $opt{u} ) ? 'distinct( message.mid )' : 'message.mid';
+}
+my $sql = "select $outf from ".join(', ', keys %table)." where ".join(' AND ', @where).';';
+
+if ( $opt{v} ) {
+	print "$sql\n";
+}
+
+if ( $opt{e} ) {
+	$dbi->do("explain $sql");
+}
+
+my $t0 = [gettimeofday];
+my $count=0;
+my $b=$opt{b};
+$b||=1;
+my @a;
+foreach ( 1..$b ) {
+	@a=exec_sql($dbi,$sql);
+	$count=$#a;
+}
+my $elapsed = tv_interval ( $t0, [gettimeofday]);
+if ( $opt{o} ) {
+	foreach ( @a ) {
+		print "$_->{mid}\t$_->{sections}\n";
+	}
+} 
+print sprintf("total: %.02f sec; number: %d; for one: %.03f sec; found %d docs\n", $elapsed, $b, $elapsed/$b, $count+1 );
+$dbi -> disconnect;
+
+sub exec_sql {
+        my ($dbi, $sql, @keys) = @_;
+        my $sth=$dbi->prepare($sql) || die;
+        $sth->execute( @keys ) || die; 
+        my $r;  
+        my @row;
+        while ( defined ( $r=$sth->fetchrow_hashref ) ) {
+                push @row, $r;
+        }               
+        $sth->finish;   
+        return @row;
+}
+
diff --git a/contrib/intarray/bench/create_test.pl b/contrib/intarray/bench/create_test.pl
new file mode 100755
index 0000000000..6434fd037d
--- /dev/null
+++ b/contrib/intarray/bench/create_test.pl
@@ -0,0 +1,73 @@
+#!/usr/bin/perl
+
+use strict;
+print <<EOT;
+create table message (
+	mid	int not null,
+	sections	int[]
+);
+create table message_section_map (
+	mid 	int not null,
+	sid	int not null
+);
+
+EOT
+
+open(MSG,">message.tmp") || die;
+open(MAP,">message_section_map.tmp") || die;
+
+srand( 1 );
+#foreach my $i ( 1..1778 ) {
+#foreach my $i ( 1..3443 ) {
+#foreach my $i ( 1..5000 ) {
+#foreach my $i ( 1..29362 ) {
+#foreach my $i ( 1..33331 ) {
+#foreach my $i ( 1..83268 ) {
+foreach my $i ( 1..200000 ) {
+	my @sect;
+	if ( rand() < 0.7 ) {
+		$sect[0] = int( (rand()**4)*100 );
+	} else {
+		my %hash;
+		@sect = grep { $hash{$_}++; $hash{$_} <= 1 } map { int( (rand()**4)*100) } 0..( int(rand()*5) );
+	}
+	if ( $#sect < 0 || rand() < 0.1 ) {
+		print MSG "$i\t\\N\n";
+	} else {
+		print MSG "$i\t{".join(',',@sect)."}\n";
+		map { print MAP "$i\t$_\n" } @sect;
+	}
+}
+close MAP;
+close MSG;
+
+copytable('message');
+copytable('message_section_map');
+
+print <<EOT;
+
+CREATE unique index message_key on message ( mid );
+--CREATE unique index message_section_map_key1 on message_section_map ( mid, sid );
+CREATE unique index message_section_map_key2 on message_section_map ( sid, mid );
+CREATE INDEX message_rdtree_idx on message using gist ( sections ) with ( islossy );
+VACUUM ANALYZE;
+
+select count(*) from message;
+select count(*) from message_section_map;
+
+
+
+EOT
+
+
+unlink 'message.tmp', 'message_section_map.tmp';
+
+sub copytable {
+	my $t = shift;
+	
+	print "COPY $t from stdin;\n";
+	open( FFF, "$t.tmp") || die;
+	while(<FFF>) { print; }
+	close FFF;
+	print "\\.\n";
+}
-- 
cgit v1.2.1