Re: Fast distinct not working as expected

[Franck Routier <franck.routier@xxxxxxxxx>]

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