Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Postgresql-How to Find Out Given Query is already run or not in Postgres Server

We can tune query is that Query whether ping or not in shared_buffers
Shared_buffers
The shared_buffers is simply an array of 8KB blocks.Each page has metadata within itself to distinguish itself as mentioned above. Before postgres checks out the data from the disk, it first does a lookup for the pages in the shared_buffers, if there is a hit then it returns the data from there itself and thereby avoiding a disk I/O. 
This Query can even tell how much data blocks came from disk and how much came from shared_buffers i.e memory.
A query plan below gives an example,
performance_test=# explain (analyze,buffers) select * from users order by userid limit 10;
    
  Limit  (cost=0.42..1.93 rows=10 width=219) (actual time=32.099..81.529 rows=10 loops=1)
    Buffers: shared read=13
    ->  Index Scan using users_userid_idx on users  (cost=0.42..150979.46 rows=1000000 width=219) (actual time=32.096..81.513 rows=10 loops=1)
          Buffers: shared read=13
  Planning time: 0.153 ms
  Execution time: 81.575 ms
 (6 rows)                                 

Shared read, means it comes from the disk and it was not cached. If the query is run again, and if the cache configuration is correct (we will discuss about it below), it will show up as shared hit.
performance_test=# explain (analyze,buffers) select * from users order by userid limit 10;
    
  Limit  (cost=0.42..1.93 rows=10 width=219) (actual time=0.030..0.052 rows=10 loops=1)
    Buffers: shared hit=13
    ->  Index Scan using users_userid_idx on users  (cost=0.42..150979.46 rows=1000000 width=219) (actual time=0.028..0.044 rows=10 loops=1)
          Buffers: shared hit=13
  Planning time: 0.117 ms
  Execution time: 0.085 ms
 (6 rows)


Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?