Jira (PDB-5534) PuppetDB query slowdown with Postgres 9.6.4

[Zaeem Arshad (Jira)]

Title: Message Title

Zaeem Arshad commented on PDB-5534   Re: PuppetDB query slowdown with Postgres 9.6.4 Austin Blatt This is the query: WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL AND deactivated > '2022-08-18T23:59:00.376Z') OR (expired IS NOT NULL and expired > '2022-08-18T23:59:00.376Z')), not_active_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT certnames.certname AS "certname" 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['bc_cluster']) and fc.types = 's') AND (jsonb_typeof("value") = 'string' AND (value = '"store-resque-cell-2"')))) )  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['bc_location']) and fc.types = 's') AND (jsonb_typeof("value") = 'string' AND (value = '"gcp_production"')))) )  sub))) AND NOT ((certnames.certname) in (SELECT certname FROM  ( SELECT not_active_nodes.certname AS "certname" FROM not_active_nodes )  sub))); Downgrading to PuppetDB 6.19.1 fixes the issue as this query is refactored in 6.20. This is not a custom query on our part btw. Add Comment   This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.466293.1662101308000.29039.1662598020045%40Atlassian.JIRA.