Re: Question about shared_buffer cache behavior

> Paul Jones <pbj(at)cmicdo(dot)com> hat am 18. März 2016 um 21:24 geschrieben:
>
>
> In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from
> a single table that uses an index appears to read the table into the
> shared_buffer cache. Then, as many times as the exact same SELECT is
> repeated in the same session, it runs blazingly fast and doesn't even
> touch the disk. All good.
>
> Now, in the *same* session, if a different SELECT from the *same* table,
> using the *same* index is run, it appears to read the entire table from
> disk again.
>
> Why is this? Is there something about the query that qualifies the
> contents of the share_buffer cache? Would this act differently for
> different kinds of indexes?

the first query reads only the tuple from heap that are matched the
where-condition.
The 2nd query with an other where-condition reads other rows than the first
query.

Keep in mind: a index search reads the index and pulls the rows that matched the
condition from the heap, no more.

Regards
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services