Hi guys, 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 Running that several times I can see, that the number of "Heap Fetches" is varying in some range (from 1 to ~80-100), sequentaly growing till ~(80-100) than starting from 1. 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
