> I still keep wondering if this table is bloated with dead tuples. Even > if you vacuum often if there's a connection with an idle transaction, > the tuples can't be reclaimed and the table would continue to grow. I used to vacuum once an hour, although I've switched it to autovacuum now. It definitely could be bloated with dead tuples. I'll paste the "vacuum analyze verbose" output at the bottom of this e-mail. Would a vacuum full be a good idea? > Assuming the table's NOT bloated, you may do well to increase the > effective_cache_size, which doesn't allocate anything, <snip> > try setting it to something like 512MB or so. It's currently set to 1000MB. > If your table is bloating, and you don't have idle transactions hanging > of the database, it could be that your fsm settings are too low. fsm is currently set to 2000000. Is there any harm in setting it too high? =) Here's the vacuum analyze verbose output: INFO: vacuuming "public.transaction_facts" INFO: scanned index "transaction_facts_pkey" to remove 759969 row versions DETAIL: CPU 7.20s/2.31u sec elapsed 315.31 sec. INFO: scanned index "transaction_facts_dcms_dim_id_idx" to remove 759969 row versions DETAIL: CPU 1.29s/2.15u sec elapsed 146.98 sec. INFO: scanned index "transaction_facts_merchant_dim_id_idx" to remove 759969 row versions DETAIL: CPU 1.10s/2.10u sec elapsed 126.09 sec. INFO: scanned index "transaction_facts_transaction_date_idx" to remove 759969 row versions DETAIL: CPU 1.65s/2.40u sec elapsed 259.25 sec. INFO: scanned index "transaction_facts_transaction_id_idx" to remove 759969 row versions DETAIL: CPU 7.48s/2.85u sec elapsed 371.98 sec. INFO: scanned index "transaction_facts_product_date_idx" to remove 759969 row versions DETAIL: CPU 2.32s/2.10u sec elapsed 303.83 sec. INFO: scanned index "transaction_facts_merchant_product_date_idx" to remove 759969 row versions DETAIL: CPU 2.48s/2.31u sec elapsed 295.19 sec. INFO: scanned index "transaction_facts_merchant_date_idx" to remove 759969 row versions DETAIL: CPU 8.10s/3.35u sec elapsed 398.73 sec. INFO: scanned index "transaction_facts_success_idx" to remove 759969 row versions DETAIL: CPU 5.01s/2.84u sec elapsed 192.73 sec. INFO: scanned index "transaction_facts_failed_idx" to remove 759969 row versions DETAIL: CPU 1.03s/1.90u sec elapsed 123.00 sec. INFO: scanned index "transaction_facts_archived_idx" to remove 759969 row versions DETAIL: CPU 1.03s/1.39u sec elapsed 104.42 sec. INFO: scanned index "transaction_facts_response_code_idx" to remove 759969 row versions DETAIL: CPU 0.75s/2.17u sec elapsed 36.71 sec. INFO: scanned index "transaction_facts_transaction_source_idx" to remove 759969 row versions DETAIL: CPU 0.60s/1.75u sec elapsed 42.29 sec. INFO: scanned index "transaction_facts_transaction_id_source_idx" to remove 759969 row versions DETAIL: CPU 1.14s/1.84u sec elapsed 44.75 sec. INFO: "transaction_facts": removed 759969 row versions in 14360 pages DETAIL: CPU 0.57s/0.23u sec elapsed 45.28 sec. INFO: index "transaction_facts_pkey" now contains 2274280 row versions in 152872 pages DETAIL: 759969 index row versions were removed. 134813 index pages have been deleted, 134813 are currently reusable. CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: index "transaction_facts_dcms_dim_id_idx" now contains 2274280 row versions in 85725 pages DETAIL: 759323 index row versions were removed. 75705 index pages have been deleted, 73721 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_merchant_dim_id_idx" now contains 2274280 row versions in 80023 pages DETAIL: 759969 index row versions were removed. 71588 index pages have been deleted, 69210 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_transaction_date_idx" now contains 2274280 row versions in 144196 pages DETAIL: 759969 index row versions were removed. 126451 index pages have been deleted, 126451 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_transaction_id_idx" now contains 2274280 row versions in 150529 pages DETAIL: 759969 index row versions were removed. 130649 index pages have been deleted, 130649 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_product_date_idx" now contains 2274280 row versions in 202248 pages DETAIL: 759969 index row versions were removed. 174652 index pages have been deleted, 174652 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_merchant_product_date_idx" now contains 2274280 row versions in 202997 pages DETAIL: 759969 index row versions were removed. 175398 index pages have been deleted, 175398 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_merchant_date_idx" now contains 2274280 row versions in 203561 pages DETAIL: 759969 index row versions were removed. 175960 index pages have been deleted, 175960 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_success_idx" now contains 2274280 row versions in 78237 pages DETAIL: 759969 index row versions were removed. 70239 index pages have been deleted, 67652 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_failed_idx" now contains 2274280 row versions in 78230 pages DETAIL: 759969 index row versions were removed. 70231 index pages have been deleted, 67665 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_archived_idx" now contains 2274280 row versions in 72943 pages DETAIL: 759969 index row versions were removed. 64962 index pages have been deleted, 62363 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_response_code_idx" now contains 2274280 row versions in 16918 pages DETAIL: 759969 index row versions were removed. 8898 index pages have been deleted, 6314 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_transaction_source_idx" now contains 2274280 row versions in 14235 pages DETAIL: 759969 index row versions were removed. 6234 index pages have been deleted, 3663 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transaction_facts_transaction_id_source_idx" now contains 2274280 row versions in 18053 pages DETAIL: 759969 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "transaction_facts": found 759969 removable, 2274280 nonremovable row versions in 308142 pages DETAIL: 0 dead row versions cannot be removed yet. There were 15710471 unused item pointers. 266986 pages contain useful free space. 0 pages are entirely empty. CPU 58.00s/35.59u sec elapsed 3240.94 sec. INFO: analyzing "public.transaction_facts" INFO: "transaction_facts": scanned 15000 of 308142 pages, containing 113476 live rows and 0 dead rows; 15000 rows in sample, 2331115 estimated total rows
