I improved the SQL, this one here is a bit faster. I also include below changes to your test program, as lines to be added. The DBI connect will inevitably need some tweaking, depending on host machine.
use DBI;
#my $dbh = DBI->connect or die "hm - $@\n"; # easier when ENV vars are
+ set
my $dbh = DBI->connect('dbi:Pg:', "app_user1", "P4ss_W0rd1") or die "
+meh - no db connection - $@\n" ;
# two test lines:
is _sql() , $union;
"SQL/Pg" => \&_sql,
# and the statement itself
sub _sql {
return $dbh->selectrow_arrayref("
select array_to_string( array(
select lower(isect) || '-' || upper(isect)-1 from(
select
('['||replace(unnest(string_to_array('$TM_part1', ',')), '-', ',
+')||']')::int4range
* ('['||replace(unnest(string_to_array('$TM_part2', ',')), '-', ',
+')||']')::int4range
) as h(isect)
), ',')
")->[0] ;
}
and this is the new result:
Rate Span Perl SQL/Pg Pair oneliner
Span 8373/s -- -57% -68% -86% -100%
Perl 19261/s 130% -- -27% -69% -100%
SQL/Pg 26397/s 215% 37% -- -57% -100%
Pair 61443/s 634% 219% 133% -- -99%
oneliner 5644800/s 67313% 29207% 21284% 9087% --