tspof on Nostr: PostgreSQL's TABLESAMPLE clause allows sampling of table rows by selecting entire ...

PostgreSQL's TABLESAMPLE clause allows sampling of table rows by selecting entire pages or individual rows, which can speed up exploratory queries on large tables by avoiding full scans. The SYSTEM method samples entire pages by hashing page numbers, leading to clusters of consecutive rows being selected, which is fast but imprecise for columns correlated with physical order (e.g., timestamps). The BERNOULLI method hashes each row's line pointer, giving a more uniform random sample but at higher cost. Samples must be manually scaled to estimate totals since only sampled rows are processed. Sampling based on hashed primary keys maintains consistent sample subsets across table modifications and preserves joinability with foreign keys but requires full scans or indexes on the hash values. Sampling has limitations with filters as it precedes WHERE clauses, and joins can be inefficient due to partial matching. VACUUM and updates affect row addresses differently, impacting sample stability. The article demonstrates that while TABLESAMPLE improves query speed (e.g., reducing pages read from 18,085 to 177), choice of sampling method and table structure critically influences accuracy and performance.

Source: https://boringsql.com/posts/tablesample/



#news_en #postgresql #tablesample #databasesampling #boringsql_com