Hello fellow penitents,

I've been having a problem with a particular Postgres SQL query running slowly when executed from my Perl script. Here it is in its Perl 5 context: 
        my $sql = "SELECT o.objectid, 
                       <other fields>
            FROM       object o
            WHERE       o.accountid = " . $accountid . 
            ORDER BY o.objectname
            LIMIT 20 OFFSET 0";

        my $object_info_ref = undef;
        eval {
            my $sth=$dbh->prepare($sql);
            $sth->execute();
            $object_info_ref = $sth->fetchall_arrayref();
            $sth->finish();
        };

[download]
That's the gist of the query, and I've used logging statements to track the slowness to the $sth->execute() command:
- When the given account ID is for an account with a lot of data, $sth->execute() takes around 12 seconds to come back.
- For an account with little data, it's very fast (less than a second).

That initially seems to make sense, if you assume the query is running differently because of the data. However, the strange thing is that the exact same query, executed from pgAdmin or psql (windowed and command-line tools for Postgres) takes about 2ms to run, regardless of which account is chosen.

So my question is, is there some kind of weird interaction between DBI and Postgres to account for this slowness? Seems to me like it can't be the database, because the database tools by themselves return the results immediately.

I've tried this with the database handle ($dbh) using autocommit on and autocommit off, and seen no difference.

Thanks!

In reply to DBI execute is slow? by mlorenz

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.