Dbix::Class slow response

Question

I have a DBIx::Class query that's taking too long to complete.

All SQL below were generated by DBIx::Class.

First scenario (DBIx Simple select):

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me ORDER BY event_time DESC LIMIT 10;

DBIx query time: 0.390221s (ok)

Second scenario (DBIx Simple select using where):

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me WHERE ( proto_id = 7 ) ORDER BY event_time DESC LIMIT 10;

DBIx query time: 29.27025s!! :(

Third scenario (Using pgadmin3 to run the query above):

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me WHERE ( proto_id = 7 ) ORDER BY event_time DESC LIMIT 10;

Pgadmin query time: 25ms (ok)

The same query is pretty fast using pgdamin.

Some info:

• Catalyst 5.90091
• DBIx::Class 0.082820 (latest)
• Postgres 9.1
• I did all tests on localhost, using Catalyst internal server.
• I have no problems with any other table/column combination, it's specific with proto_id.
• Database Schema automatically generated by DBIx::Class::Schema::Loader

• proto_id definition:

  "proto_id", { data_type => "smallint", is_foreign_key => 1, is_nullable => 0 },

Anybody have a clue why DBIx is taking so long to run this simple query?

Edit 1: Column is using index (btree).

Edit 2: This is a partitioned table, I'm checking if all the sub-tables have all the indexes, but still doesn't explain why the same query is slower on DBIx::Class.

Edit 3: I did a simple DBIx::Class script and I got the same results, just to make sure the problem is not the Catalyst Framework.

Edit 4: Using tcpdump I noticed postgres is taking too long to respond, still trying...

Edit 5: Using DBI with SQL seems pretty fast, I'm almost convinced this is a DBIx::Class problem.

Answer 1

After some tests, I found the problem:

When I do the query using DBI bind_param() (As DBIx::Class does) for some reason it became very slow.

SELECT me.pf_id, me.origin_id, me.event_time, me.proto_id FROM pf me WHERE ( proto_id = ? ) ORDER BY event_time DESC LIMIT ?;

my $sth = $dbh->prepare($sql);
$sth->bind_param(1, 80, { TYPE => SQL_INTEGER });
$sth->bind_param(2, 10, { TYPE => SQL_INTEGER });
$sth->execute();

So after some time searching CPAN I've noticed that my DBD::Pg was outdated (My bad). I downloaded the source from CPAN, compiled and the problem is gone. Must be some bug from older versions.

TL;DR: If you're having problems with DBI or DBIx::Class make sure your DBI database driver is updated.