Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51545=pgsql-performance-ar@postgresql.org>)
 id 1Wb38l-000526-Iv
 for pgsql-performance@arkaria.postgresql.org; Fri, 18 Apr 2014 07:23:15 +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+M51545=pgsql-performance-ar@postgresql.org>)
 id 1Wb38k-0005Va-QU
 for pgsql-performance@arkaria.postgresql.org; Fri, 18 Apr 2014 07:23:14 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <franck.routier@axege.com>) id 1Wb38j-0005VT-3H
 for pgsql-performance@postgresql.org; Fri, 18 Apr 2014 07:23:13 +0000
Received: from smtp09.smtpout.orange.fr ([80.12.242.131]
 helo=smtp.smtpout.orange.fr)
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <franck.routier@axege.com>) id 1Wb38e-0006hJ-UQ
 for pgsql-performance@postgresql.org; Fri, 18 Apr 2014 07:23:12 +0000
Received: from axege.com ([82.127.232.162]) by mwinf5d69 with ME
 id rKP51n00G3WtTCJ03KP5oJ; Fri, 18 Apr 2014 09:23:05 +0200
X-ME-Helo: axege.com
X-ME-Date: Fri, 18 Apr 2014 09:23:05 +0200
X-ME-IP: 82.127.232.162
Received: from localhost (localhost [127.0.0.1])
 by axege.com (Postfix) with ESMTP id 43DB65010B
 for <pgsql-performance@postgresql.org>;
 Fri, 18 Apr 2014 09:23:05 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at myhostname="axege.com."
Received: from axege.com ([127.0.0.1])
 by localhost (axege.com [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id cCmbXcrH8PiB for <pgsql-performance@postgresql.org>;
 Fri, 18 Apr 2014 09:22:56 +0200 (CEST)
Received: from [10.0.0.75] (unknown [10.0.0.75])
 (using TLSv1 with cipher DHE-RSA-AES128-SHA (128/128 bits))
 (No client certificate requested)
 by axege.com (Postfix) with ESMTPSA id 9BF4B50108
 for <pgsql-performance@postgresql.org>;
 Fri, 18 Apr 2014 09:22:56 +0200 (CEST)
Message-ID: <5350D2D0.2080702@axege.com>
Date: Fri, 18 Apr 2014 09:22:56 +0200
From: Franck Routier <franck.routier@axege.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Fast distinct not working as expected
References: <534FEF1D.5010706@axege.com>
	<CAMkU=1wTdkHmNkt9euasQux8LZ=JbWBcVb+5y1=XH3cYCpPTZw@mail.gmail.com>
	<53500CBC.9030002@axege.com>
 <CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------000301010101010309040507"
X-Pg-Spam-Score: 0.3 (/)
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.
--------------000301010101010309040507
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Hi,

Le 17/04/2014 20:17, Jeff Janes a écrit :
>
>
> If there are any open transactions (even ones that have never touched
> this particular table) which started before the delete was committed,
> then the vacuum was obliged to keep those deleted records around, in
> case that open transaction happens to start caring about them.  I
> assume that the deleted rows were not randomly distributed, but rather
> were concentrated in the exact range you are now inspecting.
>
> The reindex was also obliged to carry those deleted but not yet
> uninteresting rows along to the new index.
Ok, I understand now
>  
>
>
>     In the explain analyze output, the index scan begins at 5798.912.
>     What can be happening before that ?
>
>
> The index scan reports it first *visible* row at 5798.912.  Before
> that, it was probably digging through thousands or millions of deleted
> rows, labouriously verifying that they are not visible to it (but
> still theoretically visible to someone else). 
Very clear explaination, thank you.
>
> It could be blocked on a lock or something, or you could have really
> horrible IO contention that takes 5 seconds to read two blocks.  But I
> think the other scenario is more likely.
Yes, I also think so.
>
> By the way, many people don't like silent cross-posting, as then we
> end up unknowningly answering a question here that was already
> answered elsewhere.
Yes, sorry for that. I don't like it either, this was posted by a
colleague of mine. One of those young foolish guys that prefer web
interfaces to plain mailing-lists... Gonna scold him :-)

Best regards,
Franck

--------------000301010101010309040507
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi,<br>
    <br>
    <div class="moz-cite-prefix">Le 17/04/2014 20:17, Jeff Janes a
      écrit :<br>
    </div>
    <blockquote
cite="mid:CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote"><br>
            <div><br>
            </div>
            <div>If there are any open transactions (even ones that have
              never touched this particular table) which started before
              the delete was committed, then the vacuum was obliged to
              keep those deleted records around, in case that open
              transaction happens to start caring about them.  I assume
              that the deleted rows were not randomly distributed, but
              rather were concentrated in the exact range you are now
              inspecting.</div>
            <div><br>
            </div>
            <div>The reindex was also obliged to carry those deleted but
              not yet uninteresting rows along to the new index.</div>
          </div>
        </div>
      </div>
    </blockquote>
    Ok, I understand now
    <blockquote
cite="mid:CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">
            <div> </div>
            <blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
              <div bgcolor="#FFFFFF" text="#000000"> <br>
                In the explain analyze output, the index scan begins at
                5798.912. What can be happening before that ?</div>
            </blockquote>
            <div><br>
            </div>
            <div>The index scan reports it first *visible* row at
              5798.912.  Before that, it was probably digging through
              thousands or millions of deleted rows, labouriously
              verifying that they are not visible to it (but still
              theoretically visible to someone else).  <br>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    Very clear explaination, thank you.<br>
    <blockquote
cite="mid:CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">
            <div><br>
            </div>
            <div>It could be blocked on a lock or something, or you
              could have really horrible IO contention that takes 5
              seconds to read two blocks.  But I think the other
              scenario is more likely.</div>
          </div>
        </div>
      </div>
    </blockquote>
    Yes, I also think so.<br>
    <blockquote
cite="mid:CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">
            <div><br>
            </div>
            <div>By the way, many people don't like silent
              cross-posting, as then we end up unknowningly answering a
              question here that was already answered elsewhere.</div>
          </div>
        </div>
      </div>
    </blockquote>
    Yes, sorry for that. I don't like it either, this was posted by a
    colleague of mine. One of those young foolish guys that prefer web
    interfaces to plain mailing-lists... Gonna scold him :-)<br>
    <br>
    Best regards,<br>
    Franck<br>
  </body>
</html>

--------------000301010101010309040507--