vacuum_test_v4
text/plain
Filename: vacuum_test_v4
Type: text/plain
Part: 1
postgres=# \i ~/vacuum_check_logs.sql psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist CREATE EXTENSION dblink_connect ---------------- OK (1 row) psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist SET SET CREATE TABLE CREATE INDEX CREATE INDEX psql:/home/alena/vacuum_check_logs.sql:16: INFO: vacuuming "postgres.public.vestat" psql:/home/alena/vacuum_check_logs.sql:16: INFO: finished vacuuming "postgres.public.vestat": index scans: 0 pages: 0 removed, 0 remain, 0 scanned (100.00% of total) tuples: 0 removed, 0 remain, 0 are dead but not yet removable removable cutoff: 742, which was 0 XIDs old when operation ended new relfrozenxid: 742, which is 3 XIDs ahead of previous value frozen: 0 pages from table (100.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed I/O timings: read: 0.049 ms, write: 0.000 ms avg read rate: 40.584 MB/s, avg write rate: 0.000 MB/s buffer usage: 23 hits, 2 misses, 0 dirtied WAL usage: 1 records, 0 full page images, 237 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM pg_sleep ---------- (1 row) INSERT 0 1000000 DELETE 100000 psql:/home/alena/vacuum_check_logs.sql:25: INFO: vacuuming "postgres.public.vestat" psql:/home/alena/vacuum_check_logs.sql:25: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1) psql:/home/alena/vacuum_check_logs.sql:25: INFO: table "vestat": truncated 3922 to 3530 pages psql:/home/alena/vacuum_check_logs.sql:25: INFO: finished vacuuming "postgres.public.vestat": index scans: 1 pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total) tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable removable cutoff: 744, which was 1 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable index "vestat_idx1": pages: 785 in total, 77 newly deleted, 77 currently deleted, 0 reusable I/O timings: read: 0.000 ms, write: 0.046 ms avg read rate: 0.000 MB/s, avg write rate: 0.054 MB/s buffer usage: 13990 hits, 0 misses, 4 dirtied WAL usage: 5604 records, 1 full page images, 946934 bytes system usage: CPU: user: 0.44 s, system: 0.00 s, elapsed: 0.57 s VACUUM pg_sleep ---------- (1 row) dblink_exec ------------- BEGIN (1 row) a --- (1 row) UPDATE 900000 psql:/home/alena/vacuum_check_logs.sql:38: INFO: vacuuming "postgres.public.vestat" psql:/home/alena/vacuum_check_logs.sql:38: INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1) psql:/home/alena/vacuum_check_logs.sql:38: INFO: finished vacuuming "postgres.public.vestat": index scans: 0 pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total) tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable removable cutoff: 745, which was 1 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable I/O timings: read: 0.000 ms, write: 0.056 ms avg read rate: 0.000 MB/s, avg write rate: 0.041 MB/s buffer usage: 19425 hits, 0 misses, 4 dirtied WAL usage: 2 records, 0 full page images, 309 bytes system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 0.76 s VACUUM pg_sleep ---------- (1 row) dblink_exec ------------- COMMIT (1 row) psql:/home/alena/vacuum_check_logs.sql:45: INFO: vacuuming "postgres.public.vestat" psql:/home/alena/vacuum_check_logs.sql:45: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1) psql:/home/alena/vacuum_check_logs.sql:45: INFO: finished vacuuming "postgres.public.vestat": index scans: 1 pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total) tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable removable cutoff: 746, which was 0 XIDs old when operation ended new relfrozenxid: 745, which is 3 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable index "vestat_idx1": pages: 4342 in total, 703 newly deleted, 780 currently deleted, 77 reusable I/O timings: read: 0.000 ms, write: 0.000 ms avg read rate: 0.000 MB/s, avg write rate: 0.010 MB/s buffer usage: 33013 hits, 0 misses, 3 dirtied WAL usage: 20621 records, 0 full page images, 9097649 bytes system usage: CPU: user: 2.19 s, system: 0.00 s, elapsed: 2.43 s VACUUM pg_sleep ---------- (1 row) DELETE 900000 psql:/home/alena/vacuum_check_logs.sql:52: INFO: vacuuming "postgres.public.vestat" psql:/home/alena/vacuum_check_logs.sql:52: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1) psql:/home/alena/vacuum_check_logs.sql:52: INFO: table "vestat": truncated 7512 to 0 pages psql:/home/alena/vacuum_check_logs.sql:52: INFO: finished vacuuming "postgres.public.vestat": index scans: 1 pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total) tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable removable cutoff: 747, which was 1 XIDs old when operation ended new relfrozenxid: 747, which is 2 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable index "vestat_idx1": pages: 4342 in total, 3558 newly deleted, 4338 currently deleted, 780 reusable I/O timings: read: 0.000 ms, write: 0.000 ms avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 77924 hits, 0 misses, 0 dirtied WAL usage: 34428 records, 0 full page images, 9439566 bytes system usage: CPU: user: 0.56 s, system: 0.00 s, elapsed: 1.05 s VACUUM pg_sleep ---------- (1 row) INSERT 0 1000000 UPDATE 1000000 psql:/home/alena/vacuum_check_logs.sql:61: INFO: vacuuming "postgres.public.vestat" psql:/home/alena/vacuum_check_logs.sql:61: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1) psql:/home/alena/vacuum_check_logs.sql:61: INFO: finished vacuuming "postgres.public.vestat": index scans: 1 pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total) tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable removable cutoff: 750, which was 0 XIDs old when operation ended new relfrozenxid: 749, which is 2 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable index "vestat_idx1": pages: 8295 in total, 780 newly deleted, 4338 currently deleted, 3558 reusable I/O timings: read: 39.594 ms, write: 8.168 ms avg read rate: 22.641 MB/s, avg write rate: 5.821 MB/s buffer usage: 41051 hits, 9973 misses, 2564 dirtied WAL usage: 22911 records, 157 full page images, 11330823 bytes system usage: CPU: user: 2.63 s, system: 0.03 s, elapsed: 3.44 s VACUUM pg_sleep ---------- (1 row)