Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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;
+}