From: Franck Routier <franck.routier@axege.com>
To: pgsql-performance@postgresql.org
Subject: Re: Fast distinct not working as expected
Date: Fri, 18 Apr 2014 09:22:56 +0200
Message-ID: <5350D2D0.2080702@axege.com> (raw)
In-Reply-To: <CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com>
References: <534FEF1D.5010706@axege.com>
<CAMkU=1wTdkHmNkt9euasQux8LZ=JbWBcVb+5y1=XH3cYCpPTZw@mail.gmail.com>
<53500CBC.9030002@axege.com>
<CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: franck.routier@axege.com
Subject: Re: Fast distinct not working as expected
In-Reply-To: <5350D2D0.2080702@axege.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox