On Tue, Feb 21, 2023 at 11:58 AM Christophe Pettus <x...@thebuild.com> wrote:
> > > > On Feb 21, 2023, at 09:54, Brad White <b55wh...@gmail.com> wrote: > > Any suggestions on how to proceed? > > First, look at pg_stat_user_tables to see how many inserts etc. have > occurred on the tables that are not showing an autovacuum; they may have > simply not reached the threshold yet. If they have, do a VACUUM VERBOSE > across the database and make sure that you can manually vacuum them, or if > it reports that it can't remove tuples. Running the table_bloat_check query from here https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql shows some tables with over 20MB and over 20% bloat while my threshold is set to 0.1. Before tablename | est_rows | pct_bloat | mb_bloat | table_mb Order Items | 169234 | 33 | 25.98 | 79.695 DocumentLog | 189670 | 23 | 5.52 | 23.648 After Order Items | 179860 | 28 | 22.6 | 79.695 DocumentLog | 197537 | 23 | 5.78 | 24.672 These two now show up as vacuumed in SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; So at least that table is working. tl;dr Looks to me like vacuum removed 2215 rows from the "Order Items" indexes. "There were 166068 unused item pointers." I don't see anything that looks like an error. [SQL]vacuum verbose "Order Items" INFO: vacuuming "public.Order Items" INFO: scanned index "Order Items_pkey" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: scanned index "Order Items_AuthorityNum" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.14 sec. INFO: scanned index "Order Items_CopySrcID" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.13 sec. INFO: scanned index "Order Items_DelivMethFID" to remove 2215 row versions DETAIL: CPU 0.01s/0.01u sec elapsed 0.12 sec. INFO: scanned index "Order Items_Delivery State FID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.09 sec. INFO: scanned index "Order Items_Delivery Zip Code" to remove 2215 row versions DETAIL: CPU 0.01s/0.03u sec elapsed 0.09 sec. INFO: scanned index "Order Items_DiscountFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: scanned index "Order Items_EntityWAuthorityFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.06 sec. INFO: scanned index "Order Items_ID" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.05 sec. INFO: scanned index "Order Items_InsertFlag" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.10 sec. INFO: scanned index "Order Items_ItemFlow2" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.09 sec. INFO: scanned index "Order Items_LastSerCaptureTypeID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.09 sec. INFO: scanned index "Order Items_MastInv_FID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.11 sec. INFO: scanned index "Order Items_OpCarrFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.03u sec elapsed 0.06 sec. INFO: scanned index "Order Items_OrderDate" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.06 sec. INFO: scanned index "Order Items_OrderFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: scanned index "Order Items_PowerU_FID" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.09 sec. INFO: scanned index "Order Items_Representative" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.12 sec. INFO: scanned index "Order Items_SelValidUseFID" to remove 2215 row versions DETAIL: CPU 0.03s/0.01u sec elapsed 0.06 sec. INFO: scanned index "Order Items_SerialAuthCode" to remove 2215 row versions DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: scanned index "Order Items_SerialNum" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.10 sec. INFO: scanned index "Order Items_SourceID" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.11 sec. INFO: scanned index "Order Items_StatusReasonFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.05 sec. INFO: scanned index "Order Items_SupplyOrderKey" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.11 sec. INFO: scanned index "Order Items_TrailerU_FID" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.09 sec. INFO: scanned index "Order Items_VendorFID" to remove 2215 row versions DETAIL: CPU 0.01s/0.01u sec elapsed 0.08 sec. INFO: "Order Items": removed 2215 row versions in 1289 pages DETAIL: CPU 0.00s/0.04u sec elapsed 0.03 sec. INFO: index "Order Items_pkey" now contains 149465 row versions in 961 pages DETAIL: 162 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: index "Order Items_AuthorityNum" now contains 149465 row versions in 3182 pages DETAIL: 2215 index row versions were removed. 5 index pages have been deleted, 5 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_CopySrcID" now contains 149465 row versions in 3020 pages DETAIL: 2215 index row versions were removed. 4 index pages have been deleted, 4 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_DelivMethFID" now contains 149465 row versions in 3022 pages DETAIL: 2215 index row versions were removed. 2 index pages have been deleted, 2 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_Delivery State FID" now contains 149465 row versions in 2426 pages DETAIL: 2215 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: index "Order Items_Delivery Zip Code" now contains 149465 row versions in 2285 pages DETAIL: 2215 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: index "Order Items_DiscountFID" now contains 149465 row versions in 3015 pages DETAIL: 2215 index row versions were removed. 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_EntityWAuthorityFID" now contains 149465 row versions in 1503 pages DETAIL: 2215 index row versions were removed. 27 index pages have been deleted, 27 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_ID" now contains 149465 row versions in 1130 pages DETAIL: 316 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: index "Order Items_InsertFlag" now contains 149465 row versions in 2980 pages DETAIL: 1616 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: index "Order Items_ItemFlow2" now contains 149465 row versions in 2267 pages DETAIL: 2215 index row versions were removed. 30 index pages have been deleted, 29 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_LastSerCaptureTypeID" now contains 149465 row versions in 2618 pages DETAIL: 2215 index row versions were removed. 3 index pages have been deleted, 3 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_MastInv_FID" now contains 149465 row versions in 1514 pages DETAIL: 1883 index row versions were removed. 69 index pages have been deleted, 69 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_OpCarrFID" now contains 149465 row versions in 1496 pages DETAIL: 2215 index row versions were removed. 29 index pages have been deleted, 29 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_OrderDate" now contains 149465 row versions in 1529 pages DETAIL: 240 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: index "Order Items_OrderFID" now contains 149465 row versions in 1224 pages DETAIL: 161 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: index "Order Items_PowerU_FID" now contains 149465 row versions in 1851 pages DETAIL: 2072 index row versions were removed. 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_Representative" now contains 149465 row versions in 3506 pages DETAIL: 2215 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: index "Order Items_SelValidUseFID" now contains 149465 row versions in 1753 pages DETAIL: 1995 index row versions were removed. 14 index pages have been deleted, 14 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_SerialAuthCode" now contains 149465 row versions in 3023 pages DETAIL: 2215 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: index "Order Items_SerialNum" now contains 149465 row versions in 2636 pages DETAIL: 2215 index row versions were removed. 3 index pages have been deleted, 3 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_SourceID" now contains 149465 row versions in 2948 pages DETAIL: 2215 index row versions were removed. 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_StatusReasonFID" now contains 149465 row versions in 1467 pages DETAIL: 313 index row versions were removed. 51 index pages have been deleted, 51 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_SupplyOrderKey" now contains 149465 row versions in 2916 pages DETAIL: 2215 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: index "Order Items_TrailerU_FID" now contains 149465 row versions in 2549 pages DETAIL: 2177 index row versions were removed. 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "Order Items_VendorFID" now contains 149465 row versions in 1693 pages DETAIL: 2215 index row versions were removed. 7 index pages have been deleted, 7 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "Order Items": found 64 removable, 53611 nonremovable row versions in 2591 out of 10201 pages DETAIL: 0 dead row versions cannot be removed yet. There were 166068 unused item pointers. 0 pages are entirely empty. CPU 0.14s/0.34u sec elapsed 2.42 sec. INFO: vacuuming "pg_toast.pg_toast_402310" INFO: index "pg_toast_402310_index" now contains 0 row versions in 1 pages DETAIL: 0 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: "pg_toast_402310": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. Time: 2.569s Affected rows: 0
