diff options
Diffstat (limited to 'contrib/intarray/bench')
| -rwxr-xr-x | contrib/intarray/bench/bench.pl | 140 | ||||
| -rwxr-xr-x | contrib/intarray/bench/create_test.pl | 91 |
2 files changed, 231 insertions, 0 deletions
diff --git a/contrib/intarray/bench/bench.pl b/contrib/intarray/bench/bench.pl new file mode 100755 index 0000000..a4341d1 --- /dev/null +++ b/contrib/intarray/bench/bench.pl @@ -0,0 +1,140 @@ +#!/usr/bin/perl + +# Copyright (c) 2021, PostgreSQL Global Development Group + +use strict; +use warnings; + +# 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, "message.mid = msp$sid.mid"; + push @where, "msp$sid.sid = $sid"; + $table{"message_section_map msp$sid"} = 1; + } + } +} +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}) +{ + my @plan = + map { "$_->[0]\n" } @{ $dbi->selectall_arrayref("explain $sql") }; + print @plan; +} + +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 0000000..993a457 --- /dev/null +++ b/contrib/intarray/bench/create_test.pl @@ -0,0 +1,91 @@ +#!/usr/bin/perl + +# Copyright (c) 2021, PostgreSQL Global Development Group + +# contrib/intarray/bench/create_test.pl + +use strict; +use warnings; + +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(my $msg, '>', "message.tmp") || die; +open(my $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 gist__int_ops ); +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(my $fff, '<', "$t.tmp") || die; + while (<$fff>) { print; } + close $fff; + print "\\.\n"; + return; +} |