Re: seq vs index scan in join query

On 2017-11-29 18:17:18 +0100, Laurenz Albe wrote:
> That is because the execution with the sequential scan touched
> 26492  + 80492 = 106984 blocks, while the second execution touched
> 311301 + 48510 = 359811 blocks, more than three times as many.

That's not necessarily said. What those count are buffer *accesses*,
*not* the number of distinct blocks accessed. You'll very commonly have
more buffer accesses in indexscans but still fewer total reads because a
lot of those accesses will be reads previously done in the same
scan. Just imagine a scan of an index with a leaf page pointing to 100
tuples of the same value - that'd result in at least a 100 buffer
accesses, but it'd be highly likely that they'll be in cache.

Greetings,

Andres Freund