| Subject: | Handle::Pg uses a poorly performing definition of DistinctQuery |
The definition for DistinctQuery for PostgreSQL is very slow. Replacing
it with the definition used in Handle::Oracle, below, results in a
significant performance increase in RT with a PostgreSQL backend.
Thank you for your consideration,
Ken
sub DistinctQuery {
my $self = shift;
my $statementref = shift;
my $sb = shift;
# when we have group by clause then the result set is distinct as
# it must contain only columns we group by or results of aggregate
# functions which give one result per group, so we can skip
# DISTINCTing
if ( my $group = $sb->_GroupClause ) {
$$statementref = "SELECT main.* FROM $$statementref";
$$statementref .= $group;
$$statementref .= $sb->_OrderClause;
return;
}
my $table = $sb->Table;
if ($sb->_OrderClause =~ /(?<!main)\./) {
# If we are ordering by something not in 'main', we need to GROUP
# BY and adjust the ORDER_BY accordingly
local $sb->{group_by} = [@{$sb->{group_by} || []}, {FIELD => 'id'}];
local $sb->{order_by} = [map {($_->{ALIAS} and $_->{ALIAS} ne
"main") ? {%{$_}, FIELD => "min(".$_->{FIELD}.")"}: $_} @{$sb->{order_by}}];
my $group = $sb->_GroupClause;
my $order = $sb->_OrderClause;
$$statementref = "SELECT main.* FROM ( SELECT main.id FROM
$$statementref $group $order ) distinctquery, $table main WHERE (main.id
= distinctquery.id)";
} else {
$$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id
FROM $$statementref ) distinctquery, $table main WHERE (main.id =
distinctquery.id) ";
$$statementref .= $sb->_GroupClause;
$$statementref .= $sb->_OrderClause;
}
}