Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50869=pgsql-performance-ar@postgresql.org>)
 id 1VnwQf-000438-EP
 for pgsql-performance@arkaria.postgresql.org; Tue, 03 Dec 2013 20:18:45 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50869=pgsql-performance-ar@postgresql.org>)
 id 1VnwQe-000410-Ar
 for pgsql-performance@arkaria.postgresql.org; Tue, 03 Dec 2013 20:18:44 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <Jan@mueschelsoft.de>) id 1VnwQd-00040u-ID
 for pgsql-performance@postgresql.org; Tue, 03 Dec 2013 20:18:43 +0000
Received: from dd20832.kasserver.com ([85.13.140.227])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <Jan@mueschelsoft.de>) id 1VnwQa-0003Ta-T7
 for pgsql-performance@postgresql.org; Tue, 03 Dec 2013 20:18:43 +0000
Received: from [192.168.1.3] (p4FDC2EEE.dip0.t-ipconnect.de [79.220.46.238])
 by dd20832.kasserver.com (Postfix) with ESMTPA id 97E731CA0CD7;
 Tue,  3 Dec 2013 21:18:38 +0100 (CET)
Message-ID: <529E3C9F.8070101@mueschelsoft.de>
Date: Tue, 03 Dec 2013 21:18:39 +0100
From: Jan Michel <Jan@mueschelsoft.de>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.1
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: One query run twice in parallel results in huge performance
 decrease
References: <5299103F.1040307@mueschelsoft.de>
	<CAMkU=1wyY0BCkae12-kn=hAg81TJi2Yz+9WHvPfruzzUvxbazg@mail.gmail.com>
	<52991D59.9050100@mueschelsoft.de>
 <CAMkU=1xQc9oYLA8pRM3mRBA11ogdmd1q7Veq=iSk2g29hMJjTQ@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1xQc9oYLA8pRM3mRBA11ogdmd1q7Veq=iSk2g29hMJjTQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------060000020007080000090505"
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


Jeff Janes wrote:
> I think what I would do next is EXPLAIN (without ANALYZE) one of the 
> queries repeatedly, say once a second, while the other query either 
> runs or doesn't run repeatedly, that is the other query runs for 11 
> minutes (or however it takes to run), and then sleeps for 11 minutes 
> in a loop.  Then you can see if the explain plan differs very 
> reliably, and if the transition is exactly aligned with the other 
> starting and stopping or if it is offset.

Hi Jeff,
I ran the one analyze over and over again as you proposed - but the 
result never changed.
But I think I found a solution for the problem. While browsing through 
the manual I found a statement about GIN indexes:
"For tables with GIN indexes, VACUUM (in any form) also completes any 
pending index insertions, by moving pending index entries to the 
appropriate places in the main GIN index structure". I use a gist and no 
gin index, but I tried to vacuum the (freshly filled) table, and it 
helped. It seems that the planer is simply not aware of the existence of 
the index although I run an analyze on the table right before the query.

Thank you all for your suggestions!
Jan

--------------060000020007080000090505
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">
    <br>
    <div class="moz-cite-prefix">Jeff Janes wrote:<br>
    </div>
    <blockquote
cite="mid:CAMkU=1xQc9oYLA8pRM3mRBA11ogdmd1q7Veq=iSk2g29hMJjTQ@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">I think what I would do next is
            EXPLAIN (without ANALYZE) one of the queries repeatedly, say
            once a second, while the other query either runs or doesn't
            run repeatedly, that is the other query runs for 11 minutes
            (or however it takes to run), and then sleeps for 11 minutes
            in a loop. &nbsp;Then you can see if the explain plan differs
            very reliably, and if the transition is exactly aligned with
            the other starting and stopping or if it is offset.<br>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    Hi Jeff,<br>
    I ran the one analyze over and over again as you proposed - but the
    result never changed. <br>
    But I think I found a solution for the problem. While browsing
    through the manual I found a statement about GIN indexes:<br>
    "For tables with <acronym class="ACRONYM">GIN</acronym> indexes, <tt
      class="COMMAND">VACUUM</tt> (in any form) also completes any
    pending index insertions, by moving pending index entries to the
    appropriate places in the main <acronym class="ACRONYM">GIN</acronym>
    index structure". I use a gist and no gin index, but I tried to
    vacuum the (freshly filled) table, and it helped. It seems that the
    planer is simply not aware of the existence of the index although I
    run an analyze on the table right before the query.<br>
    <br>
    Thank you all for your suggestions!<br>
    Jan<br>
  </body>
</html>

--------------060000020007080000090505--