* Quickly estimates the bloat of a collection table. * This can be +/- 20% off, but provides a good enough estimate for the collection. * For more details refer: https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql */
| 31 | * For more details refer: https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql |
| 32 | */ |
| 33 | Datum |
| 34 | get_bloat_stats_worker(PG_FUNCTION_ARGS) |
| 35 | { |
| 36 | uint64 collectionId = PG_GETARG_INT64(0); |
| 37 | |
| 38 | MongoCollection *collection = GetMongoCollectionByColId(collectionId, |
| 39 | AccessShareLock); |
| 40 | ArrayType *shardNames = NULL; |
| 41 | ArrayType *shardOids = NULL; |
| 42 | GetMongoCollectionShardOidsAndNames(collection, &shardOids, &shardNames); |
| 43 | |
| 44 | if (shardNames == NULL) |
| 45 | { |
| 46 | return PointerGetDatum(PgbsonInitEmpty()); |
| 47 | } |
| 48 | |
| 49 | StringInfo bloatEstimateQuery = makeStringInfo(); |
| 50 | appendStringInfo(bloatEstimateQuery, |
| 51 | "WITH constants AS (" |
| 52 | " SELECT %d::numeric AS bs, 23::numeric AS hdr, 8::numeric AS ma" |
| 53 | "),", |
| 54 | BLCKSZ); |
| 55 | |
| 56 | appendStringInfo(bloatEstimateQuery, |
| 57 | "null_headers AS (" |
| 58 | " SELECT " |
| 59 | " hdr+1+(sum(case when null_frac <> 0 THEN 1 else 0 END)/8) as nullhdr, " |
| 60 | " SUM((1-null_frac)*avg_width) as datawidth, " |
| 61 | " MAX(null_frac) as maxfracsum," |
| 62 | " schemaname, tablename, hdr, ma, bs " |
| 63 | " FROM pg_stats CROSS JOIN constants " |
| 64 | " WHERE schemaname = %s" |
| 65 | " AND tablename = ANY ($1)" |
| 66 | " GROUP BY schemaname, tablename, hdr, ma, bs ), ", |
| 67 | quote_literal_cstr(ApiDataSchemaName)); |
| 68 | |
| 69 | appendStringInfo(bloatEstimateQuery, |
| 70 | " data_headers AS ( " |
| 71 | " SELECT " |
| 72 | " ma, bs, hdr, schemaname, tablename, " |
| 73 | " (datawidth+(hdr+ma-(case when hdr%%ma=0 THEN ma ELSE hdr%%ma END)))::numeric AS datahdr, " |
| 74 | " (maxfracsum*(nullhdr+ma-(case when nullhdr%%ma=0 THEN ma ELSE nullhdr%%ma END))) AS nullhdr2 " |
| 75 | " FROM null_headers " |
| 76 | ")," |
| 77 | "table_estimates AS ( " |
| 78 | " SELECT schemaname, tablename, bs, " |
| 79 | " reltuples::numeric as est_rows, relpages * bs as table_bytes, " |
| 80 | " CEIL((reltuples* " |
| 81 | " (datahdr + nullhdr2 + 4 + ma - " |
| 82 | " (CASE WHEN datahdr%%ma=0 " |
| 83 | " THEN ma ELSE datahdr%%ma END)" |
| 84 | " )/(bs-20))) * bs AS expected_bytes, " |
| 85 | " reltoastrelid " |
| 86 | " FROM data_headers " |
| 87 | " JOIN pg_class ON tablename = relname " |
| 88 | " JOIN pg_namespace ON relnamespace = pg_namespace.oid " |
| 89 | " AND schemaname = nspname " |
| 90 | " WHERE pg_class.relkind = 'r' " |
nothing calls this directly
no test coverage detected