Re: Fast distinct not working as expected

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