Improve Postgresql performance #1423
Description
- Table bloat occurs over time (autovacuum is insufficient) likely due to auto vacuum configuration settings.
- Datapoint queries become slow over time (300M+ datapoints) and can be improved by adding a compression policy (see comment here)
Query:
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;
Results:
current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes
------------------+-----------------------+--------------------+--------+-------------+-----------------------------------------------------------------+--------+--------------
openremote | openremote | asset | 2750.1 | 2387148800 | asset_pkey | 13.7 | 10829824
openremote | openremote | asset | 2750.1 | 2387148800 | section_parent_path_idx | 253.6 | 240041984
openremote | openremote | asset | 2750.1 | 2387148800 | section_parent_id_idx | 13.5 | 10682368
openremote | _timescaledb_internal | _hyper_1_162_chunk | 1.0 | 16646144 | _hyper_1_162_chunk_asset_datapoint_entity_id_attribute_name_tim | 1.7 | 271794176
openremote | _timescaledb_internal | _hyper_1_162_chunk | 1.0 | 16646144 | _hyper_1_162_chunk_asset_datapoint_timestamp_idx | 0.4 | 0
openremote | _timescaledb_internal | _hyper_1_162_chunk | 1.0 | 16646144 | 162_245_asset_datapoint_pkey | 1.0 | 0
openremote | openremote | syslog_event | 1.9 | 14278656 | syslog_event_pkey | 7.4 | 87097344
openremote | _timescaledb_internal | _hyper_1_138_chunk | 1.1 | 12861440 | _hyper_1_138_chunk_asset_datapoint_timestamp_idx | 0.5 | 0
openremote | _timescaledb_internal | _hyper_1_138_chunk | 1.1 | 12861440 | _hyper_1_138_chunk_asset_datapoint_entity_id_attribute_name_tim | 1.7 | 124747776
openremote | _timescaledb_internal | _hyper_1_138_chunk | 1.1 | 12861440 | 138_209_asset_datapoint_pkey | 0.9 | 0
openremote | _timescaledb_internal | _hyper_2_161_chunk | 6.5 | 811008 | _hyper_2_161_chunk_asset_predicted_datapoint_entity_id_attribut | 5.5 | 516096
openremote | _timescaledb_internal | _hyper_2_161_chunk | 6.5 | 811008 | _hyper_2_161_chunk_asset_predicted_datapoint_entity_id_attrib_1 | 6.0 | 573440
openremote | _timescaledb_internal | _hyper_2_161_chunk | 6.5 | 811008 | 161_243_asset_predicted_datapoint_pkey | 8.1 | 811008
openremote | _timescaledb_internal | _hyper_2_161_chunk | 6.5 | 811008 | _hyper_2_161_chunk_asset_predicted_datapoint_timestamp_idx | 1.9 | 106496
openremote | _timescaledb_internal | _hyper_2_163_chunk | 1.7 | 221184 | _hyper_2_163_chunk_asset_predicted_datapoint_entity_id_attribut | 1.7 | 180224
openremote | _timescaledb_internal | _hyper_2_163_chunk | 1.7 | 221184 | 163_246_asset_predicted_datapoint_pkey | 1.7 | 188416
openremote | _timescaledb_internal | _hyper_2_163_chunk | 1.7 | 221184 | _hyper_2_163_chunk_asset_predicted_datapoint_timestamp_idx | 0.5 | 0
openremote | _timescaledb_internal | _hyper_2_163_chunk | 1.7 | 221184 | _hyper_2_163_chunk_asset_predicted_datapoint_entity_id_attrib_1 | 1.7 | 180224
openremote | public | component_config | 2.3 | 106496 | idx_compo_config_compo | 0.2 | 0
openremote | public | component_config | 2.3 | 106496 | constr_component_config_pk | 0.7 | 0
openremote | pg_catalog | pg_depend | 1.1 | 65536 | pg_depend_depender_index | 1.0 | 0
openremote | pg_catalog | pg_depend | 1.1 | 65536 | pg_depend_reference_index | 0.6 | 0
openremote | openremote | realm_ruleset | 2.1 | 65536 | tenant_ruleset_pkey | 0.3 | 0
openremote | public | databasechangelog | 2.0 | 40960 | ? | 0.0 | 0
openremote | pg_catalog | pg_class | 1.1 | 24576 | pg_class_oid_index | 0.3 | 0
openremote | pg_catalog | pg_class | 1.1 | 24576 | pg_class_relname_nsp_index | 0.6 | 0
openremote | pg_catalog | pg_class | 1.1 | 24576 | pg_class_tblspc_relfilenode_index | 0.3 | 0
openremote | public | client_attributes | 2.0 | 16384 | constraint_3c | 2.5 | 24576
openremote | pg_catalog | pg_rewrite | 1.1 | 8192 | pg_rewrite_oid_index | 0.2 | 0
openremote | pg_catalog | pg_rewrite | 1.1 | 8192 | pg_rewrite_rel_rulename_index | 0.2 | 0
openremote | pg_catalog | pg_description | 1.0 | 8192 | pg_description_o_c_o_index | 0.9 | 0
openremote | pg_catalog | pg_conversion | 1.0 | 0 | pg_conversion_name_nsp_index | 1.0 | 0
openremote | pg_catalog | pg_conversion | 1.0 | 0 | pg_conversion_oid_index | 1.0 | 0
openremote | pg_catalog | pg_database | 1.0 | 0 | pg_database_datname_index | 2.0 | 8192
openremote | pg_catalog | pg_database | 1.0 | 0 | pg_database_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_extension | 1.0 | 0 | pg_extension_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_extension | 1.0 | 0 | pg_extension_name_index | 2.0 | 8192
openremote | pg_catalog | pg_index | 0.9 | 0 | pg_index_indrelid_index | 0.3 | 0
openremote | pg_catalog | pg_index | 0.9 | 0 | pg_index_indexrelid_index | 0.3 | 0
openremote | pg_catalog | pg_inherits | 1.0 | 0 | pg_inherits_relid_seqno_index | 2.0 | 8192
openremote | pg_catalog | pg_inherits | 1.0 | 0 | pg_inherits_parent_index | 2.0 | 8192
openremote | pg_catalog | pg_init_privs | 1.0 | 0 | pg_init_privs_o_c_o_index | 1.0 | 0
openremote | pg_catalog | pg_language | 1.0 | 0 | pg_language_name_index | 2.0 | 8192
openremote | pg_catalog | pg_language | 1.0 | 0 | pg_language_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_namespace | 1.0 | 0 | pg_namespace_nspname_index | 2.0 | 8192
openremote | pg_catalog | pg_namespace | 1.0 | 0 | pg_namespace_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_opclass | 1.0 | 0 | pg_opclass_am_name_nsp_index | 0.7 | 0
openremote | pg_catalog | pg_opclass | 1.0 | 0 | pg_opclass_oid_index | 0.7 | 0
openremote | pg_catalog | pg_operator | 1.0 | 0 | pg_operator_oid_index | 0.3 | 0
openremote | pg_catalog | pg_operator | 1.0 | 0 | pg_operator_oprname_l_r_n_index | 0.5 | 0
openremote | pg_catalog | pg_opfamily | 1.0 | 0 | pg_opfamily_am_name_nsp_index | 1.0 | 0
openremote | pg_catalog | pg_opfamily | 1.0 | 0 | pg_opfamily_oid_index | 1.0 | 0
openremote | pg_catalog | pg_proc | 1.0 | 0 | pg_proc_proname_args_nsp_index | 0.4 | 0
openremote | pg_catalog | pg_proc | 1.0 | 0 | pg_proc_oid_index | 0.1 | 0
openremote | pg_catalog | pg_range | 1.0 | 0 | pg_range_rngtypid_index | 2.0 | 8192
openremote | pg_catalog | pg_range | 1.0 | 0 | pg_range_rngmultitypid_index | 2.0 | 8192
openremote | pg_catalog | pg_shdescription | 1.0 | 0 | pg_shdescription_o_c_index | 2.0 | 8192
openremote | pg_catalog | pg_tablespace | 1.0 | 0 | pg_tablespace_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_tablespace | 1.0 | 0 | pg_tablespace_spcname_index | 2.0 | 8192
openremote | pg_catalog | pg_trigger | 0.9 | 0 | pg_trigger_tgconstraint_index | 0.3 | 0
openremote | pg_catalog | pg_trigger | 0.9 | 0 | pg_trigger_tgrelid_tgname_index | 0.7 | 0
openremote | pg_catalog | pg_trigger | 0.9 | 0 | pg_trigger_oid_index | 0.4 | 0
openremote | pg_catalog | pg_ts_config | 1.0 | 0 | pg_ts_config_cfgname_index | 2.0 | 8192
openremote | _timescaledb_catalog | chunk_index | 1.0 | 0 | chunk_index_chunk_id_index_name_key | 0.9 | 0
openremote | pg_catalog | pg_ts_config_map | 1.0 | 0 | pg_ts_config_map_index | 2.0 | 16384
openremote | pg_catalog | pg_ts_dict | 1.0 | 0 | pg_ts_dict_dictname_index | 2.0 | 8192
openremote | pg_catalog | pg_ts_dict | 1.0 | 0 | pg_ts_dict_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_ts_parser | 1.0 | 0 | pg_ts_parser_prsname_index | 2.0 | 8192
openremote | pg_catalog | pg_ts_parser | 1.0 | 0 | pg_ts_parser_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_ts_template | 1.0 | 0 | pg_ts_template_tmplname_index | 2.0 | 8192
openremote | pg_catalog | pg_ts_template | 1.0 | 0 | pg_ts_template_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_type | 0.9 | 0 | pg_type_oid_index | 0.3 | 0
openremote | pg_catalog | pg_type | 0.9 | 0 | pg_type_typname_nsp_index | 0.4 | 0
openremote | pg_catalog | pg_ts_config | 1.0 | 0 | pg_ts_config_oid_index | 2.0 | 8192
openremote | _timescaledb_catalog | chunk_index | 1.0 | 0 | chunk_index_hypertable_id_hypertable_index_name_idx | 0.2 | 0
openremote | _timescaledb_internal | _hyper_1_136_chunk | 0.0 | 0 | 136_206_asset_datapoint_pkey | 0.0 | 147251200
openremote | _timescaledb_internal | _hyper_1_136_chunk | 0.0 | 0 | _hyper_1_136_chunk_asset_datapoint_entity_id_attribute_name_tim | 0.0 | 263708672
openremote | _timescaledb_internal | _hyper_1_136_chunk | 0.0 | 0 | _hyper_1_136_chunk_asset_datapoint_timestamp_idx | 0.0 | 78020608
openremote | pg_catalog | pg_aggregate | 1.0 | 0 | pg_aggregate_fnoid_index | 0.7 | 0
openremote | pg_catalog | pg_am | 1.0 | 0 | pg_am_name_index | 2.0 | 8192
openremote | pg_catalog | pg_am | 1.0 | 0 | pg_am_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_amop | 1.0 | 0 | pg_amop_fam_strat_index | 1.2 | 8192
openremote | pg_catalog | pg_amop | 1.0 | 0 | pg_amop_opr_fam_index | 1.2 | 8192
openremote | pg_catalog | pg_amop | 1.0 | 0 | pg_amop_oid_index | 1.0 | 0
openremote | pg_catalog | pg_amproc | 1.0 | 0 | pg_amproc_fam_proc_index | 1.7 | 16384
openremote | pg_catalog | pg_amproc | 1.0 | 0 | pg_amproc_oid_index | 1.3 | 8192
openremote | pg_catalog | pg_attrdef | 1.0 | 0 | pg_attrdef_adrelid_adnum_index | 0.5 | 0
openremote | pg_catalog | pg_attrdef | 1.0 | 0 | pg_attrdef_oid_index | 0.5 | 0
openremote | pg_catalog | pg_attribute | 1.0 | 0 | pg_attribute_relid_attnam_index | 0.4 | 0
openremote | pg_catalog | pg_attribute | 1.0 | 0 | pg_attribute_relid_attnum_index | 0.3 | 0
openremote | pg_catalog | pg_auth_members | 1.0 | 0 | pg_auth_members_role_member_index | 2.0 | 8192
openremote | pg_catalog | pg_auth_members | 1.0 | 0 | pg_auth_members_member_role_index | 2.0 | 8192
openremote | pg_catalog | pg_authid | 1.0 | 0 | pg_authid_rolname_index | 2.0 | 8192
openremote | pg_catalog | pg_authid | 1.0 | 0 | pg_authid_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_cast | 1.0 | 0 | pg_cast_oid_index | 2.0 | 8192
openremote | pg_catalog | pg_cast | 1.0 | 0 | pg_cast_source_target_index | 2.0 | 8192
openremote | pg_catalog | pg_collation | 0.9 | 0 | pg_collation_name_enc_nsp_index | 0.5 | 0
openremote | pg_catalog | pg_collation | 0.9 | 0 | pg_collation_oid_index | 0.5 | 0
openremote | pg_catalog | pg_constraint | 1.0 | 0 | pg_constraint_conname_nsp_index | 0.3 | 0
openremote | pg_catalog | pg_constraint | 1.0 | 0 | pg_constraint_conrelid_contypid_conname_index | 0.3 | 0
openremote | pg_catalog | pg_constraint | 1.0 | 0 | pg_constraint_contypid_index | 0.1 | 0
openremote | pg_catalog | pg_constraint | 1.0 | 0 | pg_constraint_conparentid_index | 0.1 | 0
openremote | pg_catalog | pg_constraint | 1.0 | 0 | pg_constraint_oid_index | 0.1 | 0
openremote | pg_catalog | pg_conversion | 1.0 | 0 | pg_conversion_default_index | 1.0 | 0
(104 rows)