Jira (PDB-5451) Slow CTE query after upgrade Postgres to 11 PuppetDB 6.20

[Sergey Siulin (Jira)]

Title: Message Title

Sergey Siulin commented on PDB-5451   Re: Slow CTE query after upgrade Postgres to 11 PuppetDB 6.20 Hello austin.blatt Postgresql chooses nested_join strategy because it doesn't predicate rows count on subquery (rows = 1 ?) (cost=0.00..63485.10 rows=1 width=35) (actual time=0.089..1984.836 rows=3264 loops=38) and nested_join should be better strategy here but actually we have 3264 rows and it cannot be predicted as we select values from jsonb (jsonb doesn't have inner statistics) statistics can be provided by indexes, but it cannot be used with function jsonb_each() By simple changing subquery (replace jsonb_each()) AND ((fs.certname) in ( SELECT certname from factsets LEFT JOIN environments env ON factsets.environment_id = env.id WHERE factsets.stable->'site' = '"XX"' OR factsets.volatile->'site' = '"XX"')) It decreased Execution Time to 1500 ms total So problem is in joining subquery results to main query with from_collapse_limit =4 merge behavior was changed and I got 5100ms total random_page_cost > 3 get me seqscan instead indexscan and replcaed nested_join to merge_join with 3100 ms total final plan is: Nested Loop Semi Join (cost=64692.22..64704.54 rows=1 width=59) (actual time=3163.202..3223.252 rows=2 loops=1) Output: ((factsets.stable || factsets.volatile) -> 'ipaddress'::text), factsets.certname CTE not_active_nodes -> Seq Scan on public.certnames certnames_1 (cost=0.00..323.37 rows=171 width=26) (actual time=0.050..1.634 rows=171 loops=1) Output: certnames_1.certname Filter: ((certnames_1.deactivated IS NOT NULL) OR (certnames_1.expired IS NOT NULL)) Rows Removed by Filter: 6515 -> Merge Semi Join (cost=64367.47..64367.52 rows=1 width=94) (actual time=3138.293..3142.540 rows=3250 loops=1) Output: factsets.stable, factsets.volatile, factsets.certname, fs.certname Merge Cond: (factsets.certname = fs.certname) -> Sort (cost=512.46..512.47 rows=3 width=75) (actual time=1171.151..1171.827 rows=4919 loops=1) Output: factsets.stable, factsets.volatile, factsets.certname, factsets.environment_id Output: factsets.stable, factsets.volatile, factsets.certname, factsets.environment_id Sort Key: factsets.certname Sort Method: quicksort Memory: 1142kB -> Seq Scan on public.factsets (cost=10.69..512.44 rows=3 width=75) (actual time=2.262..1136.062 rows=6515 loops=1) Output: factsets.stable, factsets.volatile, factsets.certname, factsets.environment_id Filter: ((NOT (hashed SubPlan 2)) AND ((factsets.stable || factsets.volatile) ? 'ipaddress'::text)) Rows Removed by Filter: 145 SubPlan 2 -> CTE Scan on not_active_nodes (cost=0.00..10.26 rows=171 width=32) (actual time=0.053..1.726 rows=171 loops=1) Output: not_active_nodes.certname -> Sort (cost=63855.01..63855.01 rows=1 width=35) (actual time=1966.436..1966.679 rows=3351 loops=1) Output: fs.environment_id, fs.certname Sort Key: fs.certname Sort Method: quicksort Memory: 358kB -> Subquery Scan on fs (cost=0.00..63855.00 rows=1 width=35) (actual time=0.324..1946.836 rows=3351 loops=1) Output: fs.environment_id, fs.certname Filter: ((fs.key = 'site'::text) AND (fs.value = '"XX"'::jsonb) AND (jsonb_typeof(fs.value) = 'string'::text)) Rows Removed by Filter: 1241383 -> Result (cost=0.00..37215.00 rows=666000 width=99) (actual time=0.314..1867.217 rows=1244734 loops=1) Output: factsets_1.certname, factsets_1.environment_id, ((jsonb_each((factsets_1.stable || factsets_1.volatile)))).key, ((jsonb_each((factsets_1.stable || factsets_1.volatile)))).value -> ProjectSet (cost=0.00..10575.00 rows=666000 width=67) (actual time=0.311..1703.734 rows=1244734 loops=1) Output: jsonb_each((factsets_1.stable || factsets_1.volatile)), factsets_1.certname, factsets_1.environment_id -> Seq Scan on public.factsets factsets_1 (cost=0.00..451.80 rows=6660 width=75) (actual time=0.009..2.291 rows=6660 loops=1) Output: factsets_1.id, factsets_1.certname, factsets_1."timestamp", factsets_1.environment_id, factsets_1.hash, factsets_1.producer_timestamp, factsets_1.producer_id, factsets_1.paths_hash, factsets_1.stable, factsets_1.stable_hash, factsets_1.volatile -> Nested Loop (cost=1.38..13.61 rows=1 width=53) (actual time=0.024..0.024 rows=0 loops=3250) Output: certnames.certname, c.certname Inner Unique: true -> Nested Loop (cost=0.56..6.73 rows=1 width=61) (actual time=0.014..0.015 rows=1 loops=3250) Output: certnames.id, certnames.certname, c.certname Inner Unique: true -> Index Scan using catalogs_certname_idx on public.catalogs c (cost=0.28..6.32 rows=1 width=35) (actual time=0.007..0.007 rows=1 loops=3250) Output: c.certname, c.environment_id Index Cond: (c.certname = factsets.certname) -> Index Scan using certnames_transform_certname_key on public.certnames (cost=0.28..0.41 rows=1 width=34) (actual time=0.007..0.007 rows=1 loops=3250) Output: certnames.id, certnames.certname, certnames.latest_report_id, certnames.deactivated, certnames.expired, certnames.package_hash, certnames.latest_report_timestamp, certnames.catalog_inputs_timestamp, certnames.catalog_inputs_uuid, certnames.catalog_inputs_hash Index Cond: (certnames.certname = c.certname) -> Index Scan using catalog_resources_pkey1 on public.catalog_resources resources (cost=0.81..6.78 rows=1 width=29) (actual time=0.010..0.010 rows=0 loops=3250) Output: resources.certname_id, resources.resource Index Cond: ((resources.certname_id = certnames.id) AND (resources.type = 'Class'::text) AND (resources.title = 'Wg::Mgmt'::text)) Planning Time: 3.584 ms Execution Time: 3223.577 ms P.S. I can't find any calls of CTE `inactive_nodes` in query, looks like ORM mistake Add Comment   This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.434478.1644257054000.17339.1645019100082%40Atlassian.JIRA.