Обсуждение: Question about shared_buffer cache behavior

Поиск
Список
Период
Сортировка

Question about shared_buffer cache behavior

От
Paul Jones
Дата:
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


Re: Question about shared_buffer cache behavior

От
Andreas Kretschmer
Дата:

> 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


Re: Question about shared_buffer cache behavior

От
Rakesh Kumar
Дата:
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


Re: Question about shared_buffer cache behavior

От
Дата:
On Friday, March 18, 2016 4:54 PM, Andreas Kretschmer <andreas@a-kretschmer.de>
wrote:
 >
 >
 >> Paul Jones <pbj@cmicdo.com> hat am 18. Marz 2016 um 21:24 geschrieben:
 >>
 >> 
 >> In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from

 >
 > 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.
     
Ok, thanks!  I understand now!
  
 >
 > Regards
 > --
 > Andreas Kretschmer
 > http://www.2ndQuadrant.com/
 > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services