| Ok, so I exec:ed into the container and added 'log_min_duration_statement = 20000' to postgresql.conf and restarted.
Now the postgres-container logs slow queries.
Here is a spot sample.
This query is generated by a PuppetExplorer dashboard cell showing how many Debian 11 nodes I have, i.e. the query:
operatingsystemrelease~"^11." and operatingsystem="Debian" This generates this log while the loading of the dashboard page timeout: postgres_1 | 2022-02-04 16:40:58.161 CET [75] LOG: duration: 23246.062 ms execute <unnamed>/C_2:
SELECT COUNT AS result_count FROM (WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL AND deactivated > '2022-01-21T15:40:33.691Z')
OR (expired IS NOT NULL and expired > '2022-01-21T15:40:33.691Z')), not_active_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT reports.corrective_change AS "latest_report_corrective_change", certnames.deactivated AS "deactivated", certnames.expired AS "expired", reports_environment.environment AS "report_environment", certnames.certname AS "certname", fs.timestamp AS "facts_timestamp", facts_environment.environment AS "facts_environment",
reports.cached_catalog_status AS "cached_catalog_status", report_statuses.status AS "latest_report_status", encode(reports.hash::bytea, 'hex') AS "latest_report_hash", catalogs.timestamp AS "catalog_timestamp", reports.noop_pending AS "latest_report_noop_pending", reports.end_time AS "report_timestamp", reports.noop AS "latest_report_noop", catalog_environment.environment AS "catalog_environment", reports.job_id AS "latest_report_job_id" FROM certnames
LEFT JOIN catalogs ON catalogs.certname = certnames.certname LEFT JOIN factsets fs ON certnames.certname = fs.certname LEFT JOIN reports ON (certnames.certname = reports.certname AND certnames.latest_report_id = reports.id) LEFT JOIN environments catalog_environment ON catalog_environment.id = catalogs.environment_id
LEFT JOIN report_statuses ON reports.status_id = report_statuses.id LEFT JOIN environments facts_environment ON facts_environment.id = fs.environment_id LEFT JOIN environments reports_environment ON reports_environment.id = reports.environment_id
WHERE ((((certnames.certname) in (SELECT certname FROM ( (SELECT fc.certname AS "certname" FROM (select certname, flattened.* from factsets fs left join lateral ( with recursive flattened_one (parent_path, parent_types, key, value, type) as
( select array[]::text[], '', (jsonb_each(fs.stable||fs.volatile)).*, 's' union all select parent_path || flattened_one.key, parent_types || flattened_one.type, sub_paths.key, sub_paths.value, sub_paths.type from flattened_one inner join lateral
( select (jsonb_each(value)).*, 's' as type where jsonb_typeof(value) = 'object' union all select generate_series::text as key, value->generate_series as value, 'i' as type from generate_series(0, jsonb_array_length(value) - 1)
where jsonb_typeof(value) = 'array' ) as sub_paths on true ) select environment_id, parent_path || key as path, parent_types || type as types, coalesce(parent_path[1], key) as name, value from flattened_one where not jsonb_typeof(value) = any(' {"array", "object"}') ) as flattened on true) fc LEFT JOIN environments env ON fc.environment_id = env.id
WHERE ((fc.path = (array['operatingsystemrelease']) and fc.types = 's') AND (value#>>'{}')::text ~ $1)) ) sub)) AND ((certnames.certname) in (SELECT certname FROM ( (SELECT fc.certname AS "certname" FROM (select certname, flattened.* from factsets fs left join lateral ( with recursive flattened_one (parent_path, parent_types, key, value, type) as
( select array[]::text[], '', (jsonb_each(fs.stable||fs.volatile)).*, 's' union all select parent_path || flattened_one.key, parent_types || flattened_one.type, sub_paths.key, sub_paths.value, sub_paths.type from flattened_one inner join lateral
( select (jsonb_each(value)).*, 's' as type where jsonb_typeof(value) = 'object' union all select generate_series::text as key, value->generate_series as value, 'i' as type from generate_series(0, jsonb_array_length(value) - 1)
where jsonb_typeof(value) = 'array' ) as sub_paths on true ) select environment_id, parent_path || key as path, parent_types || type as types, coalesce(parent_path[1], key) as name, value from flattened_one where not jsonb_typeof(value) = any('{"array", "object"} ') ) as flattened on true) fc
LEFT JOIN environments env ON fc.environment_id = env.id WHERE ((fc.path = (array['operatingsystem']) and fc.types = 's') AND (jsonb_typeof("value") = $2 AND (value = $3)))) ) sub))) AND NOT ((certnames.certname) in (SELECT certname FROM ( SELECT not_active_nodes.certname AS "certname" FROM not_active_nodes ) sub)))) results_to_count postgres_1 | 2022-02-04 16:40:58.161 CET [75] DETAIL: parameters: $1 = '^11.', $2 = 'string', $3 = '"Debian"' I extract the SQL syntax and replace $1, $2 and $3 with their respective values and store this to /tmp/q.txt : SELECT COUNT AS result_count FROM (WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL AND deactivated > '2022-01-21T15:40:33.691Z')
OR (expired IS NOT NULL and expired > '2022-01-21T15:40:33.691Z')), not_active_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT reports.corrective_change AS "latest_report_corrective_change", certnames.deactivated AS "deactivated", certnames.expired AS "expired", reports_environment.environment AS "report_environment", certnames.certname AS "certname", fs.timestamp AS "facts_timestamp", facts_environment.environment AS "facts_environment",
reports.cached_catalog_status AS "cached_catalog_status", report_statuses.status AS "latest_report_status", encode(reports.hash::bytea, 'hex') AS "latest_report_hash", catalogs.timestamp AS "catalog_timestamp", reports.noop_pending AS "latest_report_noop_pending", reports.end_time AS "report_timestamp", reports.noop AS "latest_report_noop", catalog_environment.environment AS "catalog_environment", reports.job_id AS "latest_report_job_id" FROM certnames
LEFT JOIN catalogs ON catalogs.certname = certnames.certname LEFT JOIN factsets fs ON certnames.certname = fs.certname LEFT JOIN reports ON (certnames.certname = reports.certname AND certnames.latest_report_id = reports.id) LEFT JOIN environments catalog_environment ON catalog_environment.id = catalogs.environment_id
LEFT JOIN report_statuses ON reports.status_id = report_statuses.id LEFT JOIN environments facts_environment ON facts_environment.id = fs.environment_id LEFT JOIN environments reports_environment ON reports_environment.id = reports.environment_id
WHERE ((((certnames.certname) in (SELECT certname FROM ( (SELECT fc.certname AS "certname" FROM (select certname, flattened.* from factsets fs left join lateral ( with recursive flattened_one (parent_path, parent_types, key, value, type) as
( select array[]::text[], '', (jsonb_each(fs.stable||fs.volatile)).*, 's' union all select parent_path || flattened_one.key, parent_types || flattened_one.type, sub_paths.key, sub_paths.value, sub_paths.type from flattened_one inner join lateral
( select (jsonb_each(value)).*, 's' as type where jsonb_typeof(value) = 'object' union all select generate_series::text as key, value->generate_series as value, 'i' as type from generate_series(0, jsonb_array_length(value) - 1)
where jsonb_typeof(value) = 'array' ) as sub_paths on true ) select environment_id, parent_path || key as path, parent_types || type as types, coalesce(parent_path[1], key) as name, value from flattened_one where not jsonb_typeof(value) = any(' {"array", "object"}') ) as flattened on true) fc LEFT JOIN environments env ON fc.environment_id = env.id
WHERE ((fc.path = (array['operatingsystemrelease']) and fc.types = 's') AND (value#>>'{}')::text ~ '^11.')) ) sub)) AND ((certnames.certname) in (SELECT certname FROM ( (SELECT fc.certname AS "certname" FROM (select certname, flattened.* from factsets fs left join lateral ( with recursive flattened_one (parent_path, parent_types, key, value, type) as
( select array[]::text[], '', (jsonb_each(fs.stable||fs.volatile)).*, 's' union all select parent_path || flattened_one.key, parent_types || flattened_one.type, sub_paths.key, sub_paths.value, sub_paths.type from flattened_one inner join lateral
( select (jsonb_each(value)).*, 's' as type where jsonb_typeof(value) = 'object' union all select generate_series::text as key, value->generate_series as value, 'i' as type from generate_series(0, jsonb_array_length(value) - 1)
where jsonb_typeof(value) = 'array' ) as sub_paths on true ) select environment_id, parent_path || key as path, parent_types || type as types, coalesce(parent_path[1], key) as name, value from flattened_one where not jsonb_typeof(value) = any('{"array", "object"} ') ) as flattened on true) fc
LEFT JOIN environments env ON fc.environment_id = env.id WHERE ((fc.path = (array['operatingsystem']) and fc.types = 's') AND (jsonb_typeof("value") = 'string' AND (value = '"Debian"')))) ) sub))) AND NOT ((certnames.certname) in (SELECT certname FROM ( SELECT not_active_nodes.certname AS "certname" FROM not_active_nodes ) sub)))) results_to_count
- time psql -f /tmp/q.txt puppetdb puppetdb
result_count
--------------
63
(1 row)
real 0m1.023s
user 0m0.025s
sys 0m0.012s Ok, the query works. I have 63 Debian 11 machines. |
