attach.txt

text/plain

Download

without:

Overhead  Shared Ob  Symbol
   7.13%  postgres   [.] heap_page_prune_and_freeze
   6.46%  postgres   [.] heap_prune_record_unchanged_lp_normal
   5.78%  [kernel]   [k] _raw_spin_unlock_irqrestore
   4.51%  postgres   [.] heap_prepare_freeze_tuple
   4.38%  postgres   [.] HeapTupleSatisfiesVacuumHorizon
   4.04%  postgres   [.] heap_page_is_all_visible
   3.79%  postgres   [.] hash_search_with_hash_value
   3.58%  [kernel]   [k] copy_page_from_iter_atomic
   3.51%  [kernel]   [k] mark_buffer_dirty
   3.28%  postgres   [.] pg_checksum_page
   2.08%  postgres   [.] pg_comp_crc32c_avx512
   1.96%  postgres   [.] heap_pre_freeze_checks
   1.90%  postgres   [.] compactify_tuples
   1.87%  postgres   [.] PageRepairFragmentation
   1.82%  postgres   [.] PageSetChecksumCopy
   1.75%  libc.so.6  [.] __memmove_evex_unaligned_erms
   1.72%  postgres   [.] heap_log_freeze_cmp
   1.72%  postgres   [.] log_heap_prune_and_freeze
   1.43%  postgres   [.] LWLockReleaseInternal
   1.36%  postgres   [.] heap_freeze_prepared_tuples
   1.30%  postgres   [.] AdvanceXLInsertBuffer
   1.30%  postgres   [.] TransactionIdPrecedes
   0.96%  postgres   [.] HeapTupleSatisfiesVacuum
   0.91%  [kernel]   [k] filemap_get_entry
   0.87%  [kernel]   [k] ext4_da_write_end
   0.84%  postgres   [.] GetPrivateRefCountEntry
   0.83%  [kernel]   [k] fault_in_readable
   0.81%  postgres   [.] heap_tuple_needs_eventual_freeze
   0.80%  postgres   [.] TransactionIdDidCommit
   0.71%  [kernel]   [k] __block_commit_write
   0.69%  postgres   [.] LWLockAttemptLock
   0.64%  postgres   [.] TransactionLogFetch
   0.62%  postgres   [.] heap_page_prune_execute
   0.62%  [kernel]   [k] refill_stock
   0.57%  postgres   [.] LockBufHdr
   0.53%  [kernel]   [k] lruvec_stat_mod_folio
   0.52%  [kernel]   [k] refill_obj_stock
   0.51%  postgres   [.] TransactionIdFollows



reshke=# \timing
Timing is on.
reshke=# copy zz from program 'yes 2 | head -n 180000000';
COPY 180000000
Time: 58795.832 ms (00:58.796)
reshke=# copy zz from program 'yes 2 | head -n 180000000';

delete from zz where (REPLACE(REPLACE(ctid::text, '(', '{'), ')', '}')::int[])[2] = 7 ;

VACUUM FREEZE zz;
COPY 180000000
Time: 62297.357 ms (01:02.297)
DELETE 1592921
Time: 373495.158 ms (06:13.495)
VACUUM
Time: 199150.554 ms (03:19.151)



with:

7.07%  postgres   [.] heap_prune_record_unchanged_lp_normal
   6.67%  postgres   [.] heap_page_prune_and_freeze
   5.87%  [kernel]   [k] _raw_spin_unlock_irqrestore
   4.48%  postgres   [.] HeapTupleSatisfiesVacuumHorizon
   4.05%  postgres   [.] heap_prepare_freeze_tuple
   4.00%  [kernel]   [k] mark_buffer_dirty
   3.92%  [kernel]   [k] copy_page_from_iter_atomic
   3.59%  postgres   [.] heap_page_is_all_visible
   3.58%  postgres   [.] pg_checksum_page
   3.42%  postgres   [.] hash_search_with_hash_value
   2.26%  postgres   [.] pg_comp_crc32c_avx512
   2.12%  postgres   [.] PageRepairFragmentation
   2.07%  postgres   [.] PageSetChecksumCopy
   1.89%  postgres   [.] heap_pre_freeze_checks
   1.83%  postgres   [.] heap_log_freeze_cmp
   1.65%  [kernel]   [k] filemap_get_entry
   1.64%  postgres   [.] compactify_tuples
   1.53%  postgres   [.] LWLockReleaseInternal
   1.50%  postgres   [.] log_heap_prune_and_freeze
   1.49%  postgres   [.] heap_freeze_prepared_tuples
   1.40%  libc.so.6  [.] __memmove_evex_unaligned_erms
   1.38%  postgres   [.] AdvanceXLInsertBuffer
   1.04%  postgres   [.] HeapTupleSatisfiesVacuum
   1.03%  [kernel]   [k] __block_commit_write
   0.96%  postgres   [.] LWLockAttemptLock
   0.92%  [kernel]   [k] ext4_da_write_end
   0.90%  postgres   [.] heap_tuple_needs_eventual_freeze
   0.80%  postgres   [.] GetPrivateRefCountEntry
   0.78%  [kernel]   [k] fault_in_readable
   0.75%  postgres   [.] TransactionIdDidCommit
   0.72%  [kernel]   [k] lruvec_stat_mod_folio
   0.69%  [kernel]   [k] refill_stock
   0.69%  postgres   [.] TransactionLogFetch
   0.64%  [kernel]   [k] refill_obj_stock
   0.61%  postgres   [.] XLogInsert
   0.56%  postgres   [.] heap_page_prune_execute
   0.56%  [kernel]   [k] _raw_spin_lock
   0.50%  postgres   [.] uint32_hash


reshke=# copy zz from program 'yes 2 | head -n 180000000';
copy zz from program 'yes 2 | head -n 180000000';

delete from zz where (REPLACE(REPLACE(ctid::text, '(', '{'), ')', '}')::int[])[2] = 7 ;
COPY 180000000
Time: 57489.665 ms (00:57.490)
COPY 180000000
Time: 60711.024 ms (01:00.711)
DELETE 1592921
Time: 375481.858 ms (06:15.482)
reshke=#
VACUUM FREEZE zz;
VACUUM
Time: 196442.340 ms (03:16.442)