Times Reported Time consuming queries #1

x Hide

SELECT "bus_bus".id FROM "bus_bus" WHERE (("bus_bus"."create_date" > '2024-09-29 09:44:55') AND ("bus_bus"."channel" in ('["pointspring_master","calendar.alarm",19532]', '["pointspring_master","mail.channel",318]', '["pointspring_master","mail.channel",319]', '["pointspring_master","mail.channel",320]', '["pointspring_master","mail.channel",321]', '["pointspring_master","mail.channel",455]', '["pointspring_master","mail.channel",466]', '["pointspring_master","res.partner",19532]', '["pointspring_master","ir.needaction",19532]'))) ORDER BY "bus_bus"."id";

Date: 2024-09-29 09:45:46
Duration: 1s125ms

SELECT "bus_bus".id FROM "bus_bus" WHERE (("bus_bus"."create_date" > '2024-09-29 09:44:55') AND ("bus_bus"."channel" in ('"bundle_changed"', '"notify_branch_changed_1296"', '["pointspring_master","calendar.alarm",53360]', '["pointspring_master","res.partner",53360]', '["pointspring_master","ir.needaction",53360]'))) ORDER BY "bus_bus"."id";

Date: 2024-09-29 09:45:46
Duration: 1s99ms

SELECT "bus_bus".id FROM "bus_bus" WHERE (("bus_bus"."create_date" > '2024-09-29 09:44:55') AND ("bus_bus"."channel" in ('"bundle_changed"', '"notify_branch_changed_974"', '["pointspring_master","calendar.alarm",19512]', '["pointspring_master","mail.channel",419]', '["pointspring_master","mail.channel",420]', '["pointspring_master","mail.channel",421]', '["pointspring_master","mail.channel",489]', '["pointspring_master","mail.channel",557]', '["pointspring_master","res.partner",19512]', '["pointspring_master","ir.needaction",19512]'))) ORDER BY "bus_bus"."id";

Date: 2024-09-29 09:45:46
Duration: 1s64ms

x Hide

Times Reported Time consuming queries #2

x Hide

update product_warehouse_inventory set sft_stk_qty = 0 where id = ( select id from product_warehouse_inventory where product_id = (190964) and warehouse_id = (35));

Date: 2024-09-29 12:00:48
Duration: 534ms

update product_warehouse_inventory set sft_stk_qty = 0 where id = ( select id from product_warehouse_inventory where product_id = (209391) and warehouse_id = (35));

Date: 2024-09-29 12:01:39
Duration: 269ms

update product_warehouse_inventory set sft_stk_qty = 0 where id = ( select id from product_warehouse_inventory where product_id = (193946) and warehouse_id = (35));

Date: 2024-09-29 11:50:13
Duration: 232ms

x Hide

Times Reported Time consuming queries #3

x Hide

select pwi.product_id, pt.min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pt.min_product_uom_qty > 0 and pwi.stocking_flag = True and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0)) <= 0 and pwi.warehouse_id = 37 and pwi.inventory_vendor_id = 3092 and COALESCE(pt.discontinued, False) = False and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False UNION ALL select pwi.product_id, abs(((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0))) as min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pwi.stocking_flag = False and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0)) < 0 and pwi.warehouse_id = 37 and pwi.inventory_vendor_id = 3092 and COALESCE(pt.discontinued, False) = False and COALESCE(pt.never_stock, False) = True and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False UNION ALL select pwi.product_id, COALESCE(pwi.qty_on_perm_order, 0) as min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pwi.stocking_flag = True and COALESCE(pwi.qty_on_perm_order, 0) > 0 and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - (COALESCE(pwi.qty_reserve, 0) + COALESCE(pwi.qty_on_perm_order, 0))) < 0 and pt.discontinued = False and pwi.warehouse_id = 37 and pwi.inventory_vendor_id = 3092 and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False;

Date: 2024-09-29 12:09:14
Duration: 1s932ms

select pwi.product_id, pt.min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pt.min_product_uom_qty > 0 and pwi.stocking_flag = True and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0)) <= 0 and pwi.warehouse_id = 41 and pwi.inventory_vendor_id = 3105 and COALESCE(pt.discontinued, False) = False and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False UNION ALL select pwi.product_id, abs(((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0))) as min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pwi.stocking_flag = False and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0)) < 0 and pwi.warehouse_id = 41 and pwi.inventory_vendor_id = 3105 and COALESCE(pt.discontinued, False) = False and COALESCE(pt.never_stock, False) = True and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False UNION ALL select pwi.product_id, COALESCE(pwi.qty_on_perm_order, 0) as min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pwi.stocking_flag = True and COALESCE(pwi.qty_on_perm_order, 0) > 0 and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - (COALESCE(pwi.qty_reserve, 0) + COALESCE(pwi.qty_on_perm_order, 0))) < 0 and pt.discontinued = False and pwi.warehouse_id = 41 and pwi.inventory_vendor_id = 3105 and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False;

Date: 2024-09-29 12:17:37
Duration: 1s905ms

select pwi.product_id, pt.min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pt.min_product_uom_qty > 0 and pwi.stocking_flag = True and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0)) <= 0 and pwi.warehouse_id = 37 and pwi.inventory_vendor_id = 2978 and COALESCE(pt.discontinued, False) = False and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False UNION ALL select pwi.product_id, abs(((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0))) as min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pwi.stocking_flag = False and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - COALESCE(pwi.qty_reserve, 0)) < 0 and pwi.warehouse_id = 37 and pwi.inventory_vendor_id = 2978 and COALESCE(pt.discontinued, False) = False and COALESCE(pt.never_stock, False) = True and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False UNION ALL select pwi.product_id, COALESCE(pwi.qty_on_perm_order, 0) as min_product_uom_qty from product_warehouse_inventory pwi LEFT JOIN product_product as pp ON pwi.product_id = pp.id LEFT JOIN product_template as pt ON pt.id = pp.product_tmpl_id where pt.is_core = False and pwi.stocking_flag = True and COALESCE(pwi.qty_on_perm_order, 0) > 0 and ((COALESCE(pwi.qty_available, 0) + COALESCE(pwi.qty_on_order, 0)) - (COALESCE(pwi.qty_reserve, 0) + COALESCE(pwi.qty_on_perm_order, 0))) < 0 and pt.discontinued = False and pwi.warehouse_id = 37 and pwi.inventory_vendor_id = 2978 and COALESCE(pt.superceded, False) = False and COALESCE(pt.obsolete, False) = False;

Date: 2024-09-29 12:03:20
Duration: 1s871ms

x Hide

Times Reported Time consuming queries #4

x Hide

SELECT -- BLOATQUERY current_database() as database, schemaname as schema_name, tblname as table_name, bs * tblpages AS real_size, (tblpages - est_tblpages_ff) * bs AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float ELSE 0 END AS bloat_ratio -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages, ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, CASE WHEN current_setting('server_version_num')::integer < 80300 THEN 27 ELSE 23 END + CASE WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count(*)) / 8 ELSE 0::int END +0 AS tpl_hdr_size, sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.attname = att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind = 'r' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ORDER BY 2, 3) AS s) AS s2) AS s3 where not is_na and schemaname || '.' || tblname in ('pg_catalog.pg_trigger', 'pg_catalog.pg_shdescription', 'pg_catalog.pg_publication', 'pg_catalog.pg_user', 'pg_catalog.pg_stat_activity', 'pg_catalog.pg_stat_archiver', 'pg_catalog.pg_stat_xact_user_tables', 'pg_catalog.pg_timezone_names', 'pg_catalog.pg_statio_user_indexes', 'pg_catalog.pg_stat_wal_receiver', 'pg_catalog.pg_ts_config_map', 'pg_catalog.pg_stat_bgwriter', 'pg_catalog.pg_init_privs', 'pg_catalog.pg_publication_rel', 'pg_catalog.pg_opclass', 'pg_catalog.pg_stat_progress_create_index', 'pg_catalog.pg_foreign_table', 'pg_catalog.pg_stat_replication', 'pg_catalog.pg_matviews', 'pg_catalog.pg_seclabel', 'pg_catalog.pg_timezone_abbrevs', 'pg_catalog.pg_statio_sys_indexes', 'pg_catalog.pg_tablespace', 'pg_catalog.pg_replication_origin', 'pg_catalog.pg_subscription_rel', 'pg_catalog.pg_stat_gssapi', 'pg_catalog.pg_rewrite', 'pg_catalog.pg_default_acl', 'pg_catalog.pg_publication_tables', 'pg_catalog.pg_available_extensions', 'pg_catalog.pg_stat_sys_indexes', 'pg_catalog.pg_constraint', 'pg_catalog.pg_depend', 'pg_catalog.pg_roles', 'pg_catalog.pg_sequences', 'pg_catalog.pg_prepared_xacts', 'pg_catalog.pg_ts_parser', 'pg_catalog.pg_collation', 'pg_catalog.pg_statio_all_tables', 'pg_catalog.pg_stat_user_tables', 'pg_catalog.pg_stat_user_indexes', 'pg_catalog.pg_stat_progress_copy', 'pg_catalog.pg_aggregate', 'pg_catalog.pg_cursors', 'pg_catalog.pg_replication_slots', 'pg_catalog.pg_settings', 'pg_catalog.pg_stat_subscription', 'pg_catalog.pg_proc', 'pg_catalog.pg_stat_all_tables', 'pg_catalog.pg_stat_all_indexes', 'pg_catalog.pg_stat_ssl', 'pg_catalog.pg_policies', 'pg_catalog.pg_stat_progress_cluster', 'pg_catalog.pg_stat_user_functions', 'pg_catalog.pg_amop', 'pg_catalog.pg_views', 'pg_catalog.pg_stat_xact_all_tables', 'pg_catalog.pg_statio_user_tables', 'pg_catalog.pg_rules', 'pg_catalog.pg_transform', 'pg_catalog.pg_sequence', 'pg_catalog.pg_seclabels', 'pg_catalog.pg_stat_database', 'pg_catalog.pg_extension', 'pg_catalog.pg_stat_progress_basebackup', 'pg_catalog.pg_amproc', 'pg_catalog.pg_stat_xact_user_functions', 'pg_catalog.pg_attribute', 'pg_catalog.pg_stat_wal', 'pg_catalog.pg_largeobject_metadata', 'pg_catalog.pg_ts_dict', 'pg_catalog.pg_prepared_statements', 'pg_catalog.pg_type', 'pg_catalog.pg_statio_user_sequences', 'pg_catalog.pg_group', 'pg_catalog.pg_range', 'pg_catalog.pg_db_role_setting', 'pg_catalog.pg_ts_template', 'pg_catalog.pg_foreign_server', 'pg_catalog.pg_policy', 'pg_catalog.pg_auth_members', 'pg_catalog.pg_indexes', 'pg_catalog.pg_stat_xact_sys_tables', 'pg_catalog.pg_operator', 'pg_catalog.pg_stat_slru', 'pg_catalog.pg_description', 'pg_catalog.pg_stat_replication_slots', 'pg_catalog.pg_stat_progress_vacuum', 'pg_catalog.pg_enum', 'pg_catalog.pg_namespace', 'pg_catalog.pg_shdepend', 'pg_catalog.pg_statio_all_sequences', 'pg_catalog.pg_statio_sys_sequences', 'pg_catalog.pg_stat_database_conflicts', 'pg_catalog.pg_inherits', 'pg_catalog.pg_shseclabel', 'pg_catalog.pg_stats_ext', 'pg_catalog.pg_user_mappings', 'pg_catalog.pg_cast', 'pg_catalog.pg_attrdef', 'pg_catalog.pg_index', 'pg_catalog.pg_am', 'pg_catalog.pg_foreign_data_wrapper', 'pg_catalog.pg_tables', 'pg_catalog.pg_subscription', 'pg_catalog.pg_language', 'pg_catalog.pg_conversion', 'pg_catalog.pg_ts_config', 'pg_catalog.pg_partitioned_table', 'pg_catalog.pg_database', 'pg_catalog.pg_statio_all_indexes', 'pg_catalog.pg_opfamily', 'pg_catalog.pg_stat_progress_analyze', 'pg_catalog.pg_statistic_ext', 'pg_catalog.pg_available_extension_versions', 'pg_catalog.pg_stats_ext_exprs', 'pg_catalog.pg_locks', 'pg_catalog.pg_stat_sys_tables', 'pg_catalog.pg_class', 'pg_catalog.pg_stats', 'pg_catalog.pg_statio_sys_tables', 'pg_catalog.pg_event_trigger', 'information_schema.role_udt_grants', 'information_schema.columns', 'information_schema.domain_udt_usage', 'information_schema.schemata', 'information_schema.collation_character_set_applicability', 'information_schema.collations', 'information_schema.parameters', 'information_schema.role_usage_grants', 'information_schema.check_constraint_routine_usage', 'information_schema.sql_features', 'information_schema.sql_sizing', 'information_schema.element_types', 'information_schema.column_column_usage', 'information_schema.routines', 'information_schema.role_routine_grants', 'information_schema.table_privileges', 'information_schema.udt_privileges', 'information_schema.foreign_tables', 'information_schema.information_schema_catalog_name', 'information_schema.foreign_data_wrapper_options', 'information_schema.user_mapping_options', 'information_schema.administrable_role_authorizations', 'information_schema.routine_privileges', 'information_schema.enabled_roles', 'information_schema.view_column_usage', 'information_schema.role_column_grants', 'information_schema.key_column_usage', 'information_schema.user_mappings', 'information_schema.attributes', 'information_schema.routine_table_usage', 'information_schema.triggered_update_columns', 'information_schema.triggers', 'information_schema.user_defined_types', 'information_schema.column_options', 'information_schema.foreign_server_options', 'information_schema.routine_column_usage', 'information_schema.usage_privileges', 'information_schema.views', 'information_schema.column_udt_usage', 'information_schema.foreign_data_wrappers', 'information_schema.foreign_servers', 'information_schema.check_constraints', 'information_schema.character_sets', 'information_schema.referential_constraints', 'information_schema.sql_implementation_info', 'information_schema.domain_constraints', 'information_schema.domains', 'information_schema.table_constraints', 'information_schema.view_table_usage', 'information_schema.foreign_table_options', 'information_schema.constraint_column_usage', 'information_schema.column_privileges', 'information_schema.constraint_table_usage', 'information_schema.routine_sequence_usage', 'information_schema.sequences', 'information_schema.tables', 'information_schema.data_type_privileges', 'information_schema.column_domain_usage', 'information_schema.routine_routine_usage', 'information_schema.role_table_grants', 'information_schema.view_routine_usage', 'information_schema.applicable_roles', 'public.pg_stat_statements_info', 'public.pg_stat_statements');

Date: 2024-09-29 19:38:59
Duration: 278ms

SELECT -- BLOATQUERY current_database() as database, schemaname as schema_name, tblname as table_name, bs * tblpages AS real_size, (tblpages - est_tblpages_ff) * bs AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float ELSE 0 END AS bloat_ratio -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages, ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, CASE WHEN current_setting('server_version_num')::integer < 80300 THEN 27 ELSE 23 END + CASE WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count(*)) / 8 ELSE 0::int END +0 AS tpl_hdr_size, sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.attname = att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind = 'r' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ORDER BY 2, 3) AS s) AS s2) AS s3 where not is_na and schemaname || '.' || tblname in ('pg_catalog.pg_inherits', 'pg_catalog.pg_statio_sys_tables', 'pg_catalog.pg_stat_user_functions', 'pg_catalog.pg_foreign_table', 'pg_catalog.pg_operator', 'pg_catalog.pg_db_role_setting', 'pg_catalog.pg_database', 'pg_catalog.pg_tables', 'pg_catalog.pg_seclabels', 'pg_catalog.pg_stat_progress_analyze', 'pg_catalog.pg_conversion', 'pg_catalog.pg_policy', 'pg_catalog.pg_stats_ext', 'pg_catalog.pg_available_extension_versions', 'pg_catalog.pg_attribute', 'pg_catalog.pg_user', 'pg_catalog.pg_index', 'pg_catalog.pg_description', 'pg_catalog.pg_replication_slots', 'pg_catalog.pg_class', 'pg_catalog.pg_largeobject_metadata', 'pg_catalog.pg_enum', 'pg_catalog.pg_locks', 'pg_catalog.pg_statio_all_tables', 'pg_catalog.pg_constraint', 'pg_catalog.pg_opfamily', 'pg_catalog.pg_stat_archiver', 'pg_catalog.pg_stat_user_indexes', 'pg_catalog.pg_stat_gssapi', 'pg_catalog.pg_trigger', 'pg_catalog.pg_seclabel', 'pg_catalog.pg_stat_xact_sys_tables', 'pg_catalog.pg_stat_replication', 'pg_catalog.pg_stat_slru', 'pg_catalog.pg_cursors', 'pg_catalog.pg_timezone_names', 'pg_catalog.pg_stat_progress_create_index', 'pg_catalog.pg_cast', 'pg_catalog.pg_shdepend', 'pg_catalog.pg_init_privs', 'pg_catalog.pg_transform', 'pg_catalog.pg_sequences', 'pg_catalog.pg_stat_sys_indexes', 'pg_catalog.pg_user_mappings', 'pg_catalog.pg_group', 'pg_catalog.pg_settings', 'pg_catalog.pg_amproc', 'pg_catalog.pg_language', 'pg_catalog.pg_ts_template', 'pg_catalog.pg_stats_ext_exprs', 'pg_catalog.pg_statio_sys_indexes', 'pg_catalog.pg_rewrite', 'pg_catalog.pg_ts_config_map', 'pg_catalog.pg_stat_activity', 'pg_catalog.pg_stat_ssl', 'pg_catalog.pg_policies', 'pg_catalog.pg_range', 'pg_catalog.pg_subscription_rel', 'pg_catalog.pg_indexes', 'pg_catalog.pg_publication_tables', 'pg_catalog.pg_stat_all_indexes', 'pg_catalog.pg_statio_sys_sequences', 'pg_catalog.pg_auth_members', 'pg_catalog.pg_stat_wal', 'pg_catalog.pg_depend', 'pg_catalog.pg_ts_config', 'pg_catalog.pg_foreign_server', 'pg_catalog.pg_stat_database_conflicts', 'pg_catalog.pg_attrdef', 'pg_catalog.pg_shdescription', 'pg_catalog.pg_foreign_data_wrapper', 'pg_catalog.pg_prepared_statements', 'pg_catalog.pg_statio_user_sequences', 'pg_catalog.pg_timezone_abbrevs', 'pg_catalog.pg_stat_all_tables', 'pg_catalog.pg_statio_all_sequences', 'pg_catalog.pg_roles', 'pg_catalog.pg_replication_origin', 'pg_catalog.pg_shseclabel', 'pg_catalog.pg_stat_xact_all_tables', 'pg_catalog.pg_stat_xact_user_functions', 'pg_catalog.pg_stat_progress_vacuum', 'pg_catalog.pg_stats', 'pg_catalog.pg_stat_sys_tables', 'pg_catalog.pg_statio_user_tables', 'pg_catalog.pg_stat_database', 'pg_catalog.pg_aggregate', 'pg_catalog.pg_default_acl', 'pg_catalog.pg_stat_user_tables', 'pg_catalog.pg_statistic_ext', 'pg_catalog.pg_namespace', 'pg_catalog.pg_ts_dict', 'pg_catalog.pg_matviews', 'pg_catalog.pg_stat_xact_user_tables', 'pg_catalog.pg_rules', 'pg_catalog.pg_am', 'pg_catalog.pg_available_extensions', 'pg_catalog.pg_stat_progress_basebackup', 'pg_catalog.pg_subscription', 'pg_catalog.pg_partitioned_table', 'pg_catalog.pg_prepared_xacts', 'pg_catalog.pg_stat_subscription', 'pg_catalog.pg_stat_replication_slots', 'pg_catalog.pg_collation', 'pg_catalog.pg_statio_all_indexes', 'pg_catalog.pg_stat_wal_receiver', 'pg_catalog.pg_tablespace', 'pg_catalog.pg_type', 'pg_catalog.pg_opclass', 'pg_catalog.pg_amop', 'pg_catalog.pg_stat_progress_cluster', 'pg_catalog.pg_event_trigger', 'pg_catalog.pg_sequence', 'pg_catalog.pg_stat_progress_copy', 'pg_catalog.pg_extension', 'pg_catalog.pg_publication_rel', 'pg_catalog.pg_proc', 'pg_catalog.pg_ts_parser', 'pg_catalog.pg_views', 'pg_catalog.pg_stat_bgwriter', 'pg_catalog.pg_statio_user_indexes', 'pg_catalog.pg_publication', 'information_schema.foreign_tables', 'information_schema.column_udt_usage', 'information_schema.routine_sequence_usage', 'information_schema.sql_sizing', 'information_schema.table_privileges', 'information_schema.view_column_usage', 'information_schema.foreign_server_options', 'information_schema.administrable_role_authorizations', 'information_schema.routines', 'information_schema.table_constraints', 'information_schema.triggers', 'information_schema.tables', 'information_schema.role_udt_grants', 'information_schema.data_type_privileges', 'information_schema.role_usage_grants', 'information_schema.information_schema_catalog_name', 'information_schema.check_constraint_routine_usage', 'information_schema.collation_character_set_applicability', 'information_schema.column_domain_usage', 'information_schema.parameters', 'information_schema.role_table_grants', 'information_schema.usage_privileges', 'information_schema.view_routine_usage', 'information_schema.applicable_roles', 'information_schema.column_column_usage', 'information_schema.constraint_column_usage', 'information_schema.key_column_usage', 'information_schema.user_mapping_options', 'information_schema.foreign_table_options', 'information_schema.enabled_roles', 'information_schema.schemata', 'information_schema.routine_routine_usage', 'information_schema.element_types', 'information_schema.constraint_table_usage', 'information_schema.foreign_data_wrapper_options', 'information_schema.collations', 'information_schema.column_privileges', 'information_schema.domain_constraints', 'information_schema.triggered_update_columns', 'information_schema.columns', 'information_schema.domains', 'information_schema.view_table_usage', 'information_schema.foreign_data_wrappers', 'information_schema.routine_column_usage', 'information_schema.role_routine_grants', 'information_schema.sql_implementation_info', 'information_schema.sql_features', 'information_schema.role_column_grants', 'information_schema.domain_udt_usage', 'information_schema.check_constraints', 'information_schema.referential_constraints', 'information_schema.routine_table_usage', 'information_schema.user_defined_types', 'information_schema.attributes', 'information_schema.character_sets', 'information_schema.udt_privileges', 'information_schema.sequences', 'information_schema.user_mappings', 'information_schema.views', 'information_schema.routine_privileges', 'information_schema.column_options', 'information_schema.foreign_servers', 'public.pg_stat_statements_info', 'public.pg_stat_statements');

Date: 2024-09-29 01:27:56
Duration: 253ms

SELECT -- BLOATQUERY current_database() as database, schemaname as schema_name, tblname as table_name, bs * tblpages AS real_size, (tblpages - est_tblpages_ff) * bs AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float ELSE 0 END AS bloat_ratio -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages, ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, CASE WHEN current_setting('server_version_num')::integer < 80300 THEN 27 ELSE 23 END + CASE WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count(*)) / 8 ELSE 0::int END +0 AS tpl_hdr_size, sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.attname = att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind = 'r' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ORDER BY 2, 3) AS s) AS s2) AS s3 where not is_na and schemaname || '.' || tblname in ('pg_catalog.pg_opfamily', 'pg_catalog.pg_cast', 'pg_catalog.pg_init_privs', 'pg_catalog.pg_seclabel', 'pg_catalog.pg_sequence', 'pg_catalog.pg_stats', 'pg_catalog.pg_available_extensions', 'pg_catalog.pg_stat_activity', 'pg_catalog.pg_foreign_table', 'pg_catalog.pg_roles', 'pg_catalog.pg_user_mappings', 'pg_catalog.pg_tablespace', 'pg_catalog.pg_stat_progress_create_index', 'pg_catalog.pg_collation', 'pg_catalog.pg_attribute', 'pg_catalog.pg_stat_progress_vacuum', 'pg_catalog.pg_namespace', 'pg_catalog.pg_shdepend', 'pg_catalog.pg_seclabels', 'pg_catalog.pg_statio_user_indexes', 'pg_catalog.pg_type', 'pg_catalog.pg_db_role_setting', 'pg_catalog.pg_available_extension_versions', 'pg_catalog.pg_ts_dict', 'pg_catalog.pg_stat_xact_all_tables', 'pg_catalog.pg_user', 'pg_catalog.pg_statio_user_tables', 'pg_catalog.pg_stat_bgwriter', 'pg_catalog.pg_stat_progress_copy', 'pg_catalog.pg_stat_database_conflicts', 'pg_catalog.pg_database', 'pg_catalog.pg_views', 'pg_catalog.pg_subscription', 'pg_catalog.pg_rewrite', 'pg_catalog.pg_depend', 'pg_catalog.pg_am', 'pg_catalog.pg_trigger', 'pg_catalog.pg_shseclabel', 'pg_catalog.pg_prepared_xacts', 'pg_catalog.pg_statio_sys_indexes', 'pg_catalog.pg_constraint', 'pg_catalog.pg_opclass', 'pg_catalog.pg_language', 'pg_catalog.pg_stats_ext', 'pg_catalog.pg_statio_all_sequences', 'pg_catalog.pg_default_acl', 'pg_catalog.pg_policies', 'pg_catalog.pg_stat_xact_sys_tables', 'pg_catalog.pg_stat_user_functions', 'pg_catalog.pg_inherits', 'pg_catalog.pg_ts_config', 'pg_catalog.pg_partitioned_table', 'pg_catalog.pg_tables', 'pg_catalog.pg_publication_tables', 'pg_catalog.pg_stat_wal_receiver', 'pg_catalog.pg_aggregate', 'pg_catalog.pg_event_trigger', 'pg_catalog.pg_conversion', 'pg_catalog.pg_foreign_server', 'pg_catalog.pg_auth_members', 'pg_catalog.pg_stat_ssl', 'pg_catalog.pg_stat_database', 'pg_catalog.pg_class', 'pg_catalog.pg_stat_wal', 'pg_catalog.pg_ts_config_map', 'pg_catalog.pg_ts_parser', 'pg_catalog.pg_stat_all_tables', 'pg_catalog.pg_stat_user_tables', 'pg_catalog.pg_replication_slots', 'pg_catalog.pg_locks', 'pg_catalog.pg_timezone_names', 'pg_catalog.pg_statio_all_tables', 'pg_catalog.pg_stat_sys_indexes', 'pg_catalog.pg_statistic_ext', 'pg_catalog.pg_prepared_statements', 'pg_catalog.pg_statio_sys_sequences', 'pg_catalog.pg_stat_subscription', 'pg_catalog.pg_group', 'pg_catalog.pg_amop', 'pg_catalog.pg_largeobject_metadata', 'pg_catalog.pg_description', 'pg_catalog.pg_enum', 'pg_catalog.pg_policy', 'pg_catalog.pg_statio_user_sequences', 'pg_catalog.pg_stat_gssapi', 'pg_catalog.pg_settings', 'pg_catalog.pg_operator', 'pg_catalog.pg_sequences', 'pg_catalog.pg_attrdef', 'pg_catalog.pg_stat_progress_basebackup', 'pg_catalog.pg_extension', 'pg_catalog.pg_replication_origin', 'pg_catalog.pg_stat_replication', 'pg_catalog.pg_stats_ext_exprs', 'pg_catalog.pg_timezone_abbrevs', 'pg_catalog.pg_stat_replication_slots', 'pg_catalog.pg_stat_xact_user_functions', 'pg_catalog.pg_transform', 'pg_catalog.pg_publication', 'pg_catalog.pg_stat_xact_user_tables', 'pg_catalog.pg_amproc', 'pg_catalog.pg_stat_progress_analyze', 'pg_catalog.pg_range', 'pg_catalog.pg_publication_rel', 'pg_catalog.pg_subscription_rel', 'pg_catalog.pg_rules', 'pg_catalog.pg_stat_archiver', 'pg_catalog.pg_cursors', 'pg_catalog.pg_stat_sys_tables', 'pg_catalog.pg_stat_user_indexes', 'pg_catalog.pg_index', 'pg_catalog.pg_stat_progress_cluster', 'pg_catalog.pg_shdescription', 'pg_catalog.pg_foreign_data_wrapper', 'pg_catalog.pg_ts_template', 'pg_catalog.pg_matviews', 'pg_catalog.pg_indexes', 'pg_catalog.pg_stat_all_indexes', 'pg_catalog.pg_statio_all_indexes', 'pg_catalog.pg_proc', 'pg_catalog.pg_statio_sys_tables', 'pg_catalog.pg_stat_slru', 'information_schema.view_column_usage', 'information_schema.column_domain_usage', 'information_schema.column_udt_usage', 'information_schema.table_constraints', 'information_schema.view_table_usage', 'information_schema.views', 'information_schema.role_usage_grants', 'information_schema.routine_column_usage', 'information_schema.parameters', 'information_schema.routine_sequence_usage', 'information_schema.user_mapping_options', 'information_schema.information_schema_catalog_name', 'information_schema.columns', 'information_schema.triggered_update_columns', 'information_schema.routine_privileges', 'information_schema.sequences', 'information_schema.sql_sizing', 'information_schema.element_types', 'information_schema.foreign_tables', 'information_schema.collation_character_set_applicability', 'information_schema.usage_privileges', 'information_schema.applicable_roles', 'information_schema.character_sets', 'information_schema.routines', 'information_schema.role_column_grants', 'information_schema.data_type_privileges', 'information_schema.column_privileges', 'information_schema.routine_table_usage', 'information_schema.check_constraint_routine_usage', 'information_schema.check_constraints', 'information_schema.routine_routine_usage', 'information_schema.foreign_servers', 'information_schema.column_column_usage', 'information_schema.constraint_table_usage', 'information_schema.role_routine_grants', 'information_schema.user_mappings', 'information_schema.foreign_data_wrapper_options', 'information_schema.administrable_role_authorizations', 'information_schema.tables', 'information_schema.column_options', 'information_schema.domains', 'information_schema.table_privileges', 'information_schema.foreign_data_wrappers', 'information_schema.view_routine_usage', 'information_schema.domain_udt_usage', 'information_schema.key_column_usage', 'information_schema.role_udt_grants', 'information_schema.foreign_server_options', 'information_schema.attributes', 'information_schema.referential_constraints', 'information_schema.sql_features', 'information_schema.udt_privileges', 'information_schema.foreign_table_options', 'information_schema.collations', 'information_schema.constraint_column_usage', 'information_schema.schemata', 'information_schema.sql_implementation_info', 'information_schema.role_table_grants', 'information_schema.triggers', 'information_schema.domain_constraints', 'information_schema.enabled_roles', 'information_schema.user_defined_types', 'public.pg_stat_statements_info', 'public.pg_stat_statements');

Date: 2024-09-29 15:00:13
Duration: 249ms