Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 24849633CB8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 30 Mar 2009 17:49:59 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 97955-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 30 Mar 2009 17:49:53 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sprite.sidhe.org (75-54-192-43.uvs.wlfrct.sbcglobal.net
	[75.54.192.43])
	by mail.postgresql.org (Postfix) with SMTP id 83AFA63393C
	for <pgsql-performance@postgresql.org>;
	Mon, 30 Mar 2009 17:49:57 -0300 (ADT)
Received: (qmail 25678 invoked by uid 1021); 30 Mar 2009 20:49:55 -0000
Received: from localhost (HELO www.sidhe.org) (127.0.0.1)
	by sprite.sidhe.org (qpsmtpd/0.33-dev) with SMTP;
	Mon, 30 Mar 2009 16:49:55 -0400
Received: from 199.172.169.32 (SquirrelMail authenticated user dan)
	by localhost with HTTP; Mon, 30 Mar 2009 16:49:55 -0400 (EDT)
Message-ID: <61646.199.172.169.32.1238446195.squirrel@localhost>
In-Reply-To: <20090330203939.GF23023@alvh.no-ip.org>
References: <58166.199.172.169.15.1238435452.squirrel@localhost>
	<603c8f070903301131p8086067ob552d09e8b21c82@mail.gmail.com>
	<56921.199.172.169.36.1238438533.squirrel@localhost>
	<dcc563d10903301238u135d82bpf498c2d40ac31977@mail.gmail.com>
	<57654.199.172.169.17.1238442145.squirrel@localhost>
	<dcc563d10903301247t58bef7cbsd0b6d3bbd1a5e046@mail.gmail.com>
	<56164.199.172.169.17.1238443348.squirrel@localhost>
	<603c8f070903301313u1a9606a3m27da187b5e376785@mail.gmail.com>
	<56504.199.172.169.35.1238445194.squirrel@localhost>
	<20090330203939.GF23023@alvh.no-ip.org>
Date: Mon, 30 Mar 2009 16:49:55 -0400 (EDT)
Subject: Re: Trying to track down weird query stalls
From: dan@sidhe.org
To: "Alvaro Herrera" <alvherre@commandprompt.com>
Cc: dan@sidhe.org,"Robert Haas" <robertmhaas@gmail.com>,
	"Scott Marlowe" <scott.marlowe@gmail.com>, pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.2
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Priority: 3
Importance: Normal
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.279 tagged_above=0 required=5 tests=AWL=0.179,
	RDNS_DYNAMIC=0.1
X-Spam-Level: 
X-Archive-Number: 200903/429
X-Sequence-Number: 33351

> dan@sidhe.org 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