Because of the way DBIx::Class does prepared statements, even innocuous queries such
$c->model('DB::Builds)->search({finished => 0})
can be extremely slow. This is because DBIx::Class prepares a PostgreSQL statement
select … from Builds where finished = ?
and since Builds is very large and there is a large fraction of rows with "finished = 1", the PostgreSQL query planner decides to implement this query with a sequential scan of the Builds table (despite the existence of an index on "finished"), which is extremely slow. It would be nice if we could tell DBIx::Class that constants should be part of the prepared statement, i.e.
select … from Builds where finished = 0
but AFAIK we can't.
NS5GKQHW26OSSNT722GGKKMIQLTS7UKSWG2CUSV5QYBUF3A24I7AC $c->model('DB')->storage->dbh_do(sub {my (undef, $dbh) = @_;local $dbh->{pg_server_prepare} = 0;$c->stash->{steps} = [ $c->model('DB::BuildSteps')->search({ 'me.busy' => 1, 'build.finished' => 0, 'build.busy' => 1 },{ join => [ 'build' ], order_by => [ 'machine' ]} ) ];});
$c->stash->{steps} = [ $c->model('DB::BuildSteps')->search({ 'me.busy' => 1, 'build.finished' => 0, 'build.busy' => 1 },{ join => [ 'build' ], order_by => [ 'machine' ]} ) ];