Re: Trying to track down weird query stalls

From: dan@sidhe.org
Subject: Re: Trying to track down weird query stalls
Date: ,
Msg-id: 61646.199.172.169.32.1238446195.squirrel@localhost
(view: Whole thread, Raw)
In response to: Re: Trying to track down weird query stalls  (Alvaro Herrera)
Responses: Re: Trying to track down weird query stalls  (Alvaro Herrera)
List: pgsql-performance

Tree view

Trying to track down weird query stalls  (, )
 Re: Trying to track down weird query stalls  (Robert Haas, )
  Re: Trying to track down weird query stalls  (, )
   Re: Trying to track down weird query stalls  (Robert Haas, )
    Re: Trying to track down weird query stalls  (, )
   Re: Trying to track down weird query stalls  (Scott Marlowe, )
 Re: Trying to track down weird query stalls  (, )
  Re: Trying to track down weird query stalls  (Scott Marlowe, )
   Re: Trying to track down weird query stalls  (, )
    Re: Trying to track down weird query stalls  (Robert Haas, )
     Re: Trying to track down weird query stalls  (, )
      Re: Trying to track down weird query stalls  (Alvaro Herrera, )
       Re: Trying to track down weird query stalls  (, )
        Re: Trying to track down weird query stalls  (Alvaro Herrera, )
         Re: Trying to track down weird query stalls  (, )
          Re: Trying to track down weird query stalls  (Alvaro Herrera, )
           Re: Trying to track down weird query stalls  (, )
            Re: Trying to track down weird query stalls  (Alvaro Herrera, )
            Re: Trying to track down weird query stalls  (Scott Carey, )

>  escribió:
>
>> where libinstance.libdate <= 1238445044
>>    and libinstance.enddate > 1238445044
>>    and libinstance.libinstanceid = libobject.libinstanceid
>>    and libinstance.architecture = ?
>
> How are you generating the explain?  My bet is that you're just
> substituting a literal in the architecture condition, but if the driver
> is smart then maybe it's preparating the query beforehand.  You'll get a
> different plan in that case.

I don't think so. Perl's DBI is involved, but the statement's in a
variable. The code in question is:

   if ($db->{debug}) {
    $db->debug("SQL is: $sql\n");
    my $rows = $db->{dbh}->selectall_arrayref("explain analyze $sql",
                                              undef, $db->{arch},
                                              $db->{basebranch});
    foreach my $row (@$rows) {
      $db->debug(join(" ", @$row). "\n");
    }
    $db->debug_stamp("Initial query done\n");
  }

  $rows = $db->{dbh}->selectall_arrayref($sql,
                     undef, $db->{arch},
                     $db->{basebranch});

There's no transform of the sql variable between the two statements, just
a quick loop over the returned rows from the explain analyze to print them
out. (I did try to make sure that the debugging bits were done the same
way as the mainline code, but I may have bobbled something)

-Dan




pgsql-performance by date:

From: Scott Carey
Date:
Subject: Re: Trying to track down weird query stalls
From: Matthew Wakeling
Date:
Subject: Re: Very specialised query