It took 160 ms since there was disk I/O involved to fetch those records from disk.Įxecution is faster if same query is re-executed, as all the blocks are still in cache of PostgreSQL server at this stage SELECT pg_stat_reset() Īggregate (cost=1747.34 rows=1 width=8) (actual time=33.760.33.761 rows=1 loops=1)
In the above example, there were 1000 blocks read from the disk to find count tuples where c_id = 1. Then check the blocks read from the disk SELECT heap_blks_read, heap_blks_hit from pg_statio_user_tables where relname='tbldummy' Now execute a query and check for the time taken to execute the same SELECT pg_stat_reset() ĮXPLAIN ANAYZE SELECT count(*) from tbldummy where c_id=1 Īggregate (cost=1747.34 rows=1 width=8) (actual time=160.269.160.269 rows=1 loops=1) VALUES (p_id_ctr,c_id_ctr,'now', random_value, CONCAT('Description for :',p_id_ctr, c_id_ctr)) INSERT INTO tblDummy (p_id,c_id,entry_time, entry_value, description ) Populate dummy data with 200000 tuples, such that there are 10000 unique p_id and for every p_id there are 200 c_id DO $$ Start PostgreSQL keeping shared_buffer set to default 128 MB $ initdb -D $/data/nfĬonnect to the server and create a dummy table tblDummy and an index on c_id CREATE Table tblDummy
Let’s execute an example and see the impact of cache on the performance. In other words, a page which is accessed only once has higher chances of eviction (as compared to a page which is accessed multiple times), in case a new page needs to be fetched by PostgreSQL into cache. At a high level, PostgreSQL follows LRU (least recently used) algorithm to identify the pages which need to be evicted from the cache.
0 kommentar(er)
