4
# make sure we are in a sane environment.
7
use Time::HiRes qw( usleep ualarm gettimeofday tv_interval );
11
getopts('d:b:s:veorauc', \%opt);
13
if ( !( scalar %opt && defined $opt{s} ) ) {
16
$0 -d DATABASE -s SECTIONS [-b NUMBER] [-v] [-e] [-o] [-r] [-a] [-u]
18
-b NUMBER -number of repeats
19
-s SECTIONS -sections, format sid1[,sid2[,sid3[...]]]]
20
-v -verbose (show SQL)
33
my $dbi=DBI->connect('DBI:Pg:dbname='.$opt{d});
42
push @where, "message.sections @ '{$opt{s}}'";
44
foreach my $sid ( split(/[,\s]+/, $opt{s} )) {
45
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 )";
50
push @where, "message.sections && '{$opt{s}}'";
52
$table{message_section_map} = 1;
53
push @where, "message.mid = message_section_map.mid";
54
push @where, "message_section_map.sid in ($opt{s})";
60
$outf = ( $opt{u} ) ? 'count( distinct message.mid )' : 'count( message.mid )';
62
$outf = ( $opt{u} ) ? 'distinct( message.mid )' : 'message.mid';
64
my $sql = "select $outf from ".join(', ', keys %table)." where ".join(' AND ', @where).';';
71
$dbi->do("explain $sql");
74
my $t0 = [gettimeofday];
80
@a=exec_sql($dbi,$sql);
83
my $elapsed = tv_interval ( $t0, [gettimeofday]);
86
print "$_->{mid}\t$_->{sections}\n";
89
print sprintf("total: %.02f sec; number: %d; for one: %.03f sec; found %d docs\n", $elapsed, $b, $elapsed/$b, $count+1 );
93
my ($dbi, $sql, @keys) = @_;
94
my $sth=$dbi->prepare($sql) || die;
95
$sth->execute( @keys ) || die;
98
while ( defined ( $r=$sth->fetchrow_hashref ) ) {