Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Postgresql Increase Performance Query (Sequential scan) Without Index -understanding For Cache Memory

  • A sequential scan i.e when there is no index and postgres has to fetch all the data from disk are a problem area for a cache like this.
  • Since a single seq scan can wipe all of the data from a cache, it is handled differently.
  • Instead of using a normal LRU/clock sweep algorithm, it uses a series of buffers of total 256 K.B in size. The below plan shows how it is handled.
test=# explain (analyze,buffers) select count(*) from emp;
   
  Aggregate  (cost=48214.95..48214.96 rows=1 width=0) (actual time=3874.445..3874.445 rows=1 loops=1)
    Buffers: shared read=35715
    ->  Seq Scan on emp  (cost=0.00..45714.96 rows=999996 width=0) (actual time=6.024..3526.606 rows=1000000 loops=1)
          Buffers: shared read=35715
  Planning time: 0.114 ms
  Execution time: 3874.509 ms

Executing the above query again.

test=# explain (analyze,buffers) select count(*) from emp;
      Aggregate  (cost=48214.95..48214.96 rows=1 width=0) (actual time=426.385..426.385 rows=1 loops=1)
    Buffers: shared hit=32 read=35683
    ->  Seq Scan on emp  (cost=0.00..45714.96 rows=999996 width=0) (actual time=0.036..285.363 rows=1000000 loops=1)
          Buffers: shared hit=32 read=35683
  Planning time: 0.048 ms
  Execution time: 426.431 ms
We can see that exactly 32 blocks have moved into memory i.e 32 * 8 = 256 KB.

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL