Thread: Question about shared_buffer cache behavior

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?

PJ

> Paul Jones <pbj@cmicdo.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

PG loads data at the block level to shared_buffers. Most likely it is
because the second sql selects different set of rows (from different
blocks) than the first sql.

On Fri, Mar 18, 2016 at 4:24 PM, Paul Jones <pbj@cmicdo.com> wrote:
> 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?
>
> PJ
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general