Документ взят из кэша поисковой машины. Адрес оригинального документа : http://www.sai.msu.su/~megera/postgres/gist/pg_sphere/bench/gentst.pl Дата изменения: Wed Aug 6 13:47:03 2003 Дата индексирования: Sat Dec 22 07:55:13 2007 Кодировка: Поисковые слова: http www.astronomy.ru forum index.php topic 4644.0.html

#!/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< 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< \\.

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