@Thomas, this tool looks very interesting! @Jimmy:
Back to the question,you might approach the problem from a different perspective.. If you run a query 2 consecutive times, from the secondon, you should be at 'full speed'. Therefore if the first run takes X seconds but the consecutive runs take only a fraction of it, then you have probably cached all the data. It might be as simple as that. Butalsotake into account: - What is written above is true when the size of the table fits in RAM - No other activity is going on. If you have other activity going on on the disk, it will pollute your results - If you read from disk, you will see read activity on the data disk, given that your are graphing it. If is cached then it does not read from disk. - Running the query with 'explain' will tell you in detail what is going on. (Maybe you forgot to create an index on Postgres and is there on Oracle?) - If you are doing sortsand the data does not fit on work_mem then you are making use of disk space, slowing down operations regards, fabio pardi On 19/09/18 05:29, Thomas Munro wrote: > On Wed, Sep 19, 2018 at 1:35 PM jimmy <mpo...@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_prewarmed into 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. >