Re: how to know whether query data from memory after pg_prewarm

On Wed, Sep 19, 2018 at 7:44 PM Cédric Villemain <cedric@2ndquadrant.com> wrote:
> Le 19/09/2018 à 05:29, Thomas Munro a écrit :
> > On Wed, Sep 19, 2018 at 1:35 PM jimmy <mpokky@126.com> wrote:
> >> I use select pg_prewarm('table1','read','main')  to load data of table1 into the memory.
> >> when I use select count(1) from table1 group by aa to query data.
> >> I find the speed of query is not fast, I wonder whether it query data from memory.
> >> And it is slower than Oracle, both of Oracle and Postgresql has same table and count of data.
> >> when pg_prewarm use 'read' mode,  the data is put into the OS cache, how to examine the table which is
pg_prewarmedinto the OS cache . 
> >> I know pg_buffercache ,but it just examine the table in the shared buffer of Postgresql, not the table in the OS
cache.
> >
> > This is a quick and dirty hack, but it might do what you want:
> >
> > https://github.com/macdice/pgdata_mincore
> >
> > Tested on FreeBSD, not sure how well it'll travel.
>
> You can use pgfincore extension for that purpose, and more.
>
> https://github.com/klando/pgfincore/blob/master/README.md

Yes, if you only want to know *how many* pages are in the OS page
cache.  pgdata_mincore shows you which PG blocks are in the page cache
in the same format as pg_buffercache, which is useful for studying
double buffering effects.  Maybe I should turn it into a patch for
pgfincore...

--
Thomas Munro
http://www.enterprisedb.com