| Query from Puppetdb 4.4.0 to PostgreSQL 9.4:
WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT fs.certname AS certname, fp.name AS name, f.value AS value, env.environment AS environment FROM factsets fs INNER JOIN facts f ON fs.id = f.factset_id INNER JOIN fact_paths fp ON f.fact_path_id = fp.id INNER JOIN value_types vt ON vt.id = f.value_type_id LEFT JOIN environments env ON fs.environment_id = env.id WHERE (fp.depth = 0 AND ((fp.name = 'ipaddress') AND ((fs.certname) in ( (SELECT c.certname AS certname FROM catalog_resources resources INNER JOIN certnames ON resources.certname_id = certnames.id INNER JOIN catalogs c ON c.certname = certnames.certname LEFT JOIN environments e ON c.environment_id = e.id LEFT JOIN resource_params_cache rpc ON rpc.resource = resources.resource WHERE (((c.certname) in ( (SELECT fs.certname AS certname FROM factsets fs INNER JOIN facts f ON fs.id = f.factset_id INNER JOIN fact_paths fp ON f.fact_path_id = fp.id INNER JOIN value_types vt ON vt.id = f.value_type_id LEFT JOIN environments env ON fs.environment_id = env.id WHERE (fp.depth = 0 AND ((fp.name = 'project') AND (f.value_string = 'project_name')))) ) ) AND ((c.certname) in ( (SELECT fs.certname AS certname FROM factsets fs INNER JOIN facts f ON fs.id = f.factset_id INNER JOIN fact_paths fp ON f.fact_path_id = fp.id INNER JOIN value_types vt ON vt.id = f.value_type_id LEFT JOIN environments env ON fs.environment_id = env.id WHERE (fp.depth = 0 AND ((fp.name = 'server_class') AND (f.value_string = 'server_class_name')))) ) ) AND ((c.certname) in ( (SELECT fs.certname AS certname FROM factsets fs INNER JOIN facts f ON fs.id = f.factset_id INNER JOIN fact_paths fp ON f.fact_path_id = fp.id INNER JOIN value_types vt ON vt.id = f.value_type_id LEFT JOIN environments env ON fs.environment_id = env.id WHERE (fp.depth = 0 AND ((fp.name = 'server_type') AND (f.value_string = 'server_type_name')))) ) ))) ) )));
|
explain of the query:
Nested Loop Left Join (cost=287.19..3294.34 rows=1 width=103) -> Nested Loop (cost=287.06..3294.17 rows=1 width=79) -> Nested Loop Semi Join (cost=286.93..3294.01 rows=1 width=87) -> Nested Loop (cost=6.50..678.42 rows=7 width=87) -> Nested Loop (cost=6.23..675.06 rows=11 width=65) -> Index Scan using fact_paths_name on fact_paths fp (cost=0.42..13.99 rows=1 width=20) Index Cond: ((name)::text = 'ipaddress'::text) Filter: (depth = 0) -> Bitmap Heap Scan on facts f (cost=5.81..659.28 rows=179 width=61) Recheck Cond: (fact_path_id = fp.id) -> Bitmap Index Scan on facts_fact_path_id_idx (cost=0.00..5.77 rows=179 width=0) Index Cond: (fact_path_id = fp.id) -> Index Scan using factsets_pkey on factsets fs (cost=0.28..0.30 rows=1 width=38) Index Cond: (id = f.factset_id) -> Hash Semi Join (cost=280.42..572.31 rows=1 width=110) Hash Cond: (c.certname = fs_3.certname) -> Hash Semi Join (cost=199.57..491.46 rows=1 width=88) Hash Cond: (c.certname = fs_1.certname) -> Hash Semi Join (cost=118.72..410.61 rows=1 width=66) Hash Cond: (c.certname = fs_2.certname) -> Nested Loop (cost=37.87..329.75 rows=1 width=44) -> Nested Loop (cost=0.55..0.79 rows=1 width=52) -> Index Scan using catalogs_certname_idx on catalogs c (cost=0.28..0.42 rows=1 width=30) Index Cond: (certname = fs.certname) -> Index Scan using certnames_transform_certname_key on certnames (cost=0.28..0.36 rows=1 width=30) Index Cond: (certname = c.certname) -> Bitmap Heap Scan on catalog_resources resources (cost=37.32..317.24 rows=1173 width=29) Recheck Cond: (certname_id = certnames.id) -> Bitmap Index Scan on catalog_resources_pkey1 (cost=0.00..37.03 rows=1173 width=0) Index Cond: (certname_id = certnames.id) -> Hash (cost=80.84..80.84 rows=1 width=22) -> Nested Loop (cost=1.12..80.84 rows=1 width=22) -> Nested Loop (cost=0.99..80.50 rows=1 width=30) -> Nested Loop (cost=0.70..72.70 rows=1 width=38) -> Index Scan using factsets_certname_idx on factsets fs_2 (cost=0.28..0.39 rows=1 width=38) Index Cond: (certname = fs.certname) -> Index Scan using facts_factset_id_idx on facts f_2 (cost=0.42..72.30 rows=1 width=24) Index Cond: (factset_id = fs_2.id) Filter: (value_string = 'server_class_name'::text) -> Index Scan using fact_paths_pkey on fact_paths fp_2 (cost=0.29..7.79 rows=1 width=8) Index Cond: (id = f_2.fact_path_id) Filter: ((depth = 0) AND ((name)::text = 'server_class'::text)) -> Index Only Scan using value_types_pkey on value_types vt_2 (cost=0.13..0.33 rows=1 width=8) Index Cond: (id = f_2.value_type_id) -> Hash (cost=80.84..80.84 rows=1 width=22) -> Nested Loop (cost=1.12..80.84 rows=1 width=22) -> Nested Loop (cost=0.99..80.50 rows=1 width=30) -> Nested Loop (cost=0.70..72.70 rows=1 width=38) -> Index Scan using factsets_certname_idx on factsets fs_1 (cost=0.28..0.39 rows=1 width=38) Index Cond: (certname = fs.certname) -> Index Scan using facts_factset_id_idx on facts f_1 (cost=0.42..72.30 rows=1 width=24) Index Cond: (factset_id = fs_1.id) Filter: (value_string = 'project_name'::text) -> Index Scan using fact_paths_pkey on fact_paths fp_1 (cost=0.29..7.79 rows=1 width=8) Index Cond: (id = f_1.fact_path_id) Filter: (value_string = 'project_name'::text) -> Index Scan using fact_paths_pkey on fact_paths fp_1 (cost=0.29..7.79 rows=1 width=8) Index Cond: (id = f_1.fact_path_id) Filter: ((depth = 0) AND ((name)::text = 'project'::text)) -> Index Only Scan using value_types_pkey on value_types vt_1 (cost=0.13..0.33 rows=1 width=8) Index Cond: (id = f_1.value_type_id) -> Hash (cost=80.84..80.84 rows=1 width=22) -> Nested Loop (cost=1.12..80.84 rows=1 width=22) -> Nested Loop (cost=0.99..80.50 rows=1 width=30) -> Nested Loop (cost=0.70..72.70 rows=1 width=38) -> Index Scan using factsets_certname_idx on factsets fs_3 (cost=0.28..0.39 rows=1 width=38) Index Cond: (certname = fs.certname) -> Index Scan using facts_factset_id_idx on facts f_3 (cost=0.42..72.30 rows=1 width=24) Index Cond: (factset_id = fs_3.id) Filter: (value_string = 'server_type_name'::text) -> Index Scan using fact_paths_pkey on fact_paths fp_3 (cost=0.29..7.79 rows=1 width=8) Index Cond: (id = f_3.fact_path_id) Filter: ((depth = 0) AND ((name)::text = 'server_type'::text)) -> Index Only Scan using value_types_pkey on value_types vt_3 (cost=0.13..0.33 rows=1 width=8) Index Cond: (id = f_3.value_type_id) -> Index Only Scan using value_types_pkey on value_types vt (cost=0.13..0.15 rows=1 width=8) Index Cond: (id = f.value_type_id) -> Index Scan using environments_pkey on environments env (cost=0.13..0.16 rows=1 width=40) Index Cond: (fs.environment_id = id)(76 rows)
|
Execution time: 0m0.603s Compared to ~30s on Puppetdb7 |
