Документ взят из кэша поисковой машины. Адрес оригинального документа : http://www.sai.msu.su/~megera/postgres/mkjoindata.pl Дата изменения: Wed May 26 22:56:30 1999 Дата индексирования: Sat Dec 22 04:34:52 2007 Кодировка: Поисковые слова: http www.astronomy.ru forum index.php topic 4644.0.html

#!/usr/local/bin/perl -w
# Crash Postgres with many joins
# Oleg Bartunov, 1999

use strict;

$| = 1;
my $njoins = undef;
my $nrows = undef;
my @nitems = ();
my $option = undef;
my $vacuum = 0;
my $explain = 0;
my $geqo = 0;
my $bad_option = '';

while ( @ARGV ) {
$option = shift @ARGV;
if ( defined ($option) ) {
if ( $option =~ /^--with-vacuum$/ ) {
$vacuum = 1;
} elsif ($option =~ /^--explain$/ ) {
$explain = 1;
} elsif ( $option =~ /^--with-geqo$/) {
$geqo = 1;
} elsif ( $option =~ /^--help$/) {
&usage;
} elsif ( $option =~ /^--joins$/) {
$njoins = shift @ARGV;
$njoins ||= '';
$bad_option .= " \t$option must be followed by number: $njoins \n" unless $njoins =~ /^\d+$/
} elsif ( $option =~ /^--rows$/) {
$nrows = shift @ARGV;
$nrows ||= '';
$bad_option .=" \t$option must be followed by number: $nrows \n" unless $nrows =~ /^\d+$/;
} else {
$bad_option .= "\Unrecognized option: $option !\n";
}
}
}

&usage ($bad_option) if ( $bad_option );
&usage ($bad_option) unless ( defined ($option) );

die (" Not enough parameters: ") unless ( defined($njoins) && defined($nrows) );
my $tables = $njoins;
my $ntables = $tables+1;
eval '@nitems = (' . '$nrows,'x$ntables . ')';

my $SQL = '';
for ( my $i = 0; $i <= $tables; $i++ ) {
my $table = 't'.$i;
$SQL .= qq^
drop table $table;
create table $table ( id int4 NOT NULL, a varchar(4));
COPY $table FROM STDIN USING DELIMITERS '|';
^;
$SQL .= join ( "\n", map { $_.'|'.'a'.$table } (1..$nitems[$i]) );
$SQL .= qq^\n\\.\n^;
$SQL .= qq^create index id_$table on $table (id);\n^;
}

# vacuum analyze
$SQL .= qq^vacuum analyze;\n^ if ( $vacuum );
print "$SQL\n";

# Query with joins
my $SELECT = '';
# Try GEQO
$SELECT .= qq^set GEQO to 'ON=$njoins';^ if ( $geqo );
$SELECT .= 'explain ' if ( $explain );
$SELECT .= 'select '. join( ', ', map { 't'.$_.'.a as t'.$_; } (0..$njoins) );
$SELECT .= "\n where " . join( ' and ', map { 't'.$_.'.id=t0.id' } (1..$njoins) );
$SELECT .= ';';

print $SELECT,"\n";

sub usage {
my $msg = shift;
print " ERROR:\n$msg\n" if $msg;
print qq^
Usage:

$0 --joins Njoins --rows Nrows [ options ]

Parameters:
Njoins - the number of joins (Njoins < Ntables)
Nrows - the number of rows

Options:
--help - print usage
--explain - do not send query but explain
--with-vacuum - vacuum analyze before query
--with-geqo - use GEQO for query

Example:
$0 --joins 14 --rows 200 --explain | psql test
^;
print "\n";
exit;
}