[PERFORM] zero value in statistics collector's result

[Yinan Li]

Hi,

 

When I use the statistics collector to see the number of IO, I always get
zero in almost all of columns. I really want to know the reason for that.

 

The result of statistics view:

# select * from pg_statio_user_tables;

 relid | schemaname | relname | heap_blks_read | heap_blks_hit |
idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit |
tidx_blks_read | tidx_blks_hit 

---++-++---+
---+--+-+++-
--

 16386 | public | tab |  0 | 0 |
0 |0 | |||


(1 row)

# select * from pg_statio_user_indexes;

 relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read |
idx_blks_hit 

---+++-+--+---+-
-

 16386 |  24581 | public | tab | idx_tab_a2   | 0 |
0

(1 row)

 

I've set:

stats_start_collector = on

stats_row_level = on

stats_block_level = on

 

and I think that the statistics collector should be running, because: 

$ ps aux|grep stats

postgres  3688  0.0  0.0   7272   648 ?Ss   00:48   0:00 postgres:
stats collector process 

postgres 29790  0.0  0.0   4004   712 pts/2S+   04:44   0:00 grep stats

 

Any help would be really appreciated.

 

Regards,

Yinan

 

[PERFORM] how to improve the performance of creating index

[Yinan Li]

Hi, all

 

 I am trying to improve the performance of creating index.

 I've set shared_buffers = 1024MB

   Effective_cache_size = 1024MB

   Work_mem = 1GB

   Maintenance_work_mem=512MB

 (I'm sure that the server process has received the SIGHUP signal)

However, when create index, I found that the memory used by Postgres is only
50MB. And it is very slow. How to make it faster?

All helps are appreciated.

 

Thanks.

Yinan