Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <vinny@xs4all.nl>) id 1TNJHO-0003oO-1t
	for pgsql-admin@postgresql.org; Sun, 14 Oct 2012 08:10:34 +0000
Received: from smtp-vbr8.xs4all.nl ([194.109.24.28])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <vinny@xs4all.nl>) id 1TNJHM-0000qm-Bp
	for pgsql-admin@postgresql.org; Sun, 14 Oct 2012 08:10:33 +0000
Received: from [10.0.0.211] (vindev.xs4all.nl [80.101.96.12])
	(authenticated bits=0)
	by smtp-vbr8.xs4all.nl (8.13.8/8.13.8) with ESMTP id q9E8AU6t054874
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-admin@postgresql.org>; Sun, 14 Oct 2012 10:10:31 +0200 (CEST)
	(envelope-from vinny@xs4all.nl)
Message-ID: <507A7375.7000903@xs4all.nl>
Date: Sun, 14 Oct 2012 10:10:29 +0200
From: Vincent <vinny@xs4all.nl>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121011 Thunderbird/16.0.1
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Query with limit takes hours
References: <COL104-W2612EF566F12BD42D730FFF5720@phx.gbl>
In-Reply-To: <COL104-W2612EF566F12BD42D730FFF5720@phx.gbl>
Content-Type: multipart/alternative;
	boundary="------------060900040202040701040101"
X-Virus-Scanned: by XS4ALL Virus Scanner
X-Pg-Spam-Score: -2.3 (--)
X-Archive-Number: 201210/42
X-Sequence-Number: 40158

This is a multi-part message in MIME format.
--------------060900040202040701040101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/14/2012 08:56 AM, henk de wit wrote:
> On PG 9.1 and 9.2 I'm running the following query:
>
> SELECT
>     *
> FROM
>     stream_store
> JOIN
>     (
>         SELECT
>             UNNEST(stream_store_ids) AS id
>         FROM
>             stream_store_version_index
>         WHERE
>             stream_id = 607106 AND
>             version = 11
>     ) AS records USING (id)
> ORDER BY
>     id DESC
>
> This takes several (10 to 20) milliseconds at most.
>
> When I add a LIMIT 1 to the end of the query, the query time goes to 
> several hours(!).
>
> The full version String of PG 9.1 is "PostgreSQL 9.1.5 on 
> x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 
> 4.4.5, 64-bit". The 9.1 machine is a socket 771 dual quad core at 
> 3.16Ghz with 64GB memory and 10 Intel x25M SSDs in a RAID5 setup on 2 
> ARECA 1680 RAID controllers. The "stream_store" table has 122 million 
> rows and is partitioned. The array that's being unnested for the join 
> has 27 entries.
>
> Any idea?

I'd look at the difference in the output of EXPLAIN for both queries, 
that's bound to tell you something...

--------------060900040202040701040101
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 10/14/2012 08:56 AM, henk de wit
      wrote:<br>
    </div>
    <blockquote cite="mid:COL104-W2612EF566F12BD42D730FFF5720@phx.gbl"
      type="cite">
      <style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style>
      <div dir="ltr">
        <div>On PG 9.1 and 9.2 I'm running the following query:</div>
        <div><br>
        </div>
        <div>SELECT&nbsp;</div>
        <div>&nbsp; &nbsp; *</div>
        <div>FROM&nbsp;</div>
        <div>&nbsp; &nbsp; stream_store&nbsp;</div>
        <div>JOIN</div>
        <div>&nbsp; &nbsp; (</div>
        <div>&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp;</div>
        <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNNEST(stream_store_ids) AS id</div>
        <div>&nbsp; &nbsp; &nbsp; &nbsp; FROM&nbsp;</div>
        <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stream_store_version_index&nbsp;</div>
        <div>&nbsp; &nbsp; &nbsp; &nbsp; WHERE&nbsp;</div>
        <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stream_id = 607106 AND</div>
        <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; version = 11</div>
        <div>&nbsp; &nbsp; ) AS records USING (id)</div>
        <div>ORDER BY&nbsp;</div>
        <div>&nbsp; &nbsp; id DESC</div>
        <div><br>
        </div>
        <div>This takes several (10 to 20) milliseconds at most.</div>
        <div><br>
        </div>
        <div>When I add a LIMIT 1 to the end of the query, the query
          time goes to several hours(!).</div>
        <div><br>
        </div>
        <div>The full version String of PG 9.1 is "PostgreSQL 9.1.5 on
          x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian
          4.4.5-8) 4.4.5, 64-bit". The 9.1 machine is a socket 771 dual
          quad core at 3.16Ghz with 64GB memory and 10 Intel x25M SSDs
          in a RAID5 setup on 2 ARECA 1680 RAID controllers. The
          "stream_store" table has 122 million rows and is partitioned.
          The array that's being unnested for the join has 27 entries.</div>
        <div><br>
        </div>
        <div>Any idea?</div>
      </div>
    </blockquote>
    <br>
    I'd look at the difference in the output of EXPLAIN for both
    queries, that's bound to tell you something...<br>
  </body>
</html>

--------------060900040202040701040101--