I'm running the process, that executes "select * from sr where sr.id=210 for update;", then some calculations and finally "update sr set usage = <somevalue> where sr.id = 210;". That operation is done in a loop.
In parallel session i'm running the query:
test=# explain (analyze, buffers) select id from sr where id = 210;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
----------------
Index Only Scan using sr_pk on sr (cost=0.57..8.59 rows=1 width=4) (actual time=0.018..1.172 rows=1 loops=1)
Index Cond: (id = 210)
Heap Fetches: 10
Buffers: shared hit=592
Planning time: 0.057 ms
Execution time: 1.183 ms
Considering that the autovacuum process is turned off (for research purposes only :) ), I was expecting the infinite growth of Heap Fetches since no cleaning of dead rows or visibility map support occurs.
Can someone explain, what else can decrease the number of heap access needed to check the rows visibility?
I'm running "PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit" with the following settings:
name | current_setting | source
------------------------------+--------------------------------+----------------------
application_name | psql | client
autovacuum | off | configuration file
autovacuum_work_mem | 1GB | configuration file
bytea_output | escape | configuration file
checkpoint_completion_target | 0.7 | configuration file
checkpoint_timeout | 30min | configuration file
client_encoding | UTF8 | client
constraint_exclusion | partition | configuration file
DateStyle | ISO, MDY | configuration file
deadlock_timeout | 1s | configuration file
default_statistics_target | 100 | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
effective_cache_size | 23GB | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
listen_addresses | * | configuration file
log_autovacuum_min_duration | 10s | configuration file
log_checkpoints | on | configuration file
log_connections | on | configuration file
log_destination | stderr | configuration file
log_directory | pg_log | configuration file
log_disconnections | on | configuration file
log_filename | postgresql-%a.log | configuration file
log_line_prefix | [%m] p=%p:%l@%v c=%u@%h/%d:%a | configuration file
log_lock_waits | on | configuration file
log_min_duration_statement | 1s | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_temp_files | 1MB | configuration file
log_timezone | Host | configuration file
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 3GB | configuration file
max_connections | 256 | configuration file
max_stack_depth | 2MB | environment variable
max_wal_size | 5408MB | configuration file
pg_stat_statements.max | 10000 | configuration file
pg_stat_statements.track | all | configuration file
shared_buffers | 7GB | configuration file
shared_preload_libraries | pg_stat_statements | configuration file
ssl | on | configuration file
TimeZone | Host | configuration file
work_mem | 162MB | configuration file
(46 rows)
Regards, Mikhail
