Created
June 1, 2017 23:13
-
-
Save npwalker/45cc8634509cdc7f0c9bca67691c2431 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| LOG: duration: 0.464 ms plan: | |
| Query Text: SELECT 1 | |
| FROM certnames | |
| WHERE certname=$1 | |
| Index Only Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=4) (actual time=0.322..0.333 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Heap Fetches: 2 | |
| Buffers: shared hit=5 | |
| LOG: duration: 19.829 ms plan: | |
| Query Text: 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 ((fs.certname = $1) AND NOT ((fs.certname) in ( (SELECT inactive_nodes.certname AS certname FROM inactive_nodes) ) ))) | |
| Hash Join (cost=1239.63..2152.17 rows=109 width=92) (actual time=19.507..19.674 rows=104 loops=1) | |
| Hash Cond: (f.fact_path_id = fp.id) | |
| Buffers: shared hit=748 | |
| CTE inactive_nodes | |
| -> Seq Scan on certnames (cost=0.00..1224.73 rows=1 width=10) (actual time=19.104..19.104 rows=0 loops=1) | |
| Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL)) | |
| Rows Removed by Filter: 49473 | |
| Buffers: shared hit=730 | |
| -> Hash Join (cost=7.94..918.91 rows=128 width=87) (actual time=19.392..19.511 rows=119 loops=1) | |
| Hash Cond: (f.value_type_id = vt.id) | |
| Buffers: shared hit=746 | |
| -> Nested Loop (cost=6.80..916.02 rows=128 width=95) (actual time=19.373..19.456 rows=119 loops=1) | |
| Buffers: shared hit=745 | |
| -> Nested Loop Left Join (cost=0.59..16.64 rows=1 width=50) (actual time=19.285..19.288 rows=1 loops=1) | |
| Buffers: shared hit=736 | |
| -> Index Scan using factsets_certname_idx on factsets fs (cost=0.44..8.46 rows=1 width=26) (actual time=19.253..19.254 rows=1 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Filter: (NOT (hashed SubPlan 2)) | |
| Buffers: shared hit=734 | |
| SubPlan 2 | |
| -> CTE Scan on inactive_nodes (cost=0.00..0.02 rows=1 width=32) (actual time=19.106..19.106 rows=0 loops=1) | |
| Buffers: shared hit=730 | |
| -> Index Scan using environments_pkey on environments env (cost=0.15..8.17 rows=1 width=40) (actual time=0.027..0.028 rows=1 loops=1) | |
| Index Cond: (fs.environment_id = id) | |
| Buffers: shared hit=2 | |
| -> Bitmap Heap Scan on facts f (cost=6.21..897.08 rows=230 width=61) (actual time=0.079..0.134 rows=119 loops=1) | |
| Recheck Cond: (factset_id = fs.id) | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.16 rows=230 width=0) (actual time=0.049..0.049 rows=160 loops=1) | |
| Index Cond: (factset_id = fs.id) | |
| Buffers: shared hit=4 | |
| -> Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.012..0.012 rows=6 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| Buffers: shared hit=1 | |
| -> Seq Scan on value_types vt (cost=0.00..1.06 rows=6 width=8) (actual time=0.002..0.003 rows=6 loops=1) | |
| Buffers: shared hit=1 | |
| -> Hash (cost=4.68..4.68 rows=183 width=21) (actual time=0.108..0.108 rows=161 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 18kB | |
| Buffers: shared hit=2 | |
| -> Seq Scan on fact_paths fp (cost=0.00..4.68 rows=183 width=21) (actual time=0.022..0.069 rows=161 loops=1) | |
| Filter: (depth = 0) | |
| Rows Removed by Filter: 34 | |
| Buffers: shared hit=2 | |
| LOG: duration: 0.184 ms plan: | |
| Query Text: SELECT 1 FROM certnames WHERE certname=$1 LIMIT 1 | |
| Limit (cost=0.41..8.43 rows=1 width=4) (actual time=0.157..0.157 rows=1 loops=1) | |
| Buffers: shared hit=5 | |
| -> Index Only Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=4) (actual time=0.157..0.157 rows=1 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Heap Fetches: 1 | |
| Buffers: shared hit=5 | |
| LOG: duration: 0.081 ms plan: | |
| Query Text: UPDATE certnames SET deactivated = NULL,expired = NULL WHERE certname=$1 AND (deactivated<$2 OR expired<$3) | |
| Update on certnames (cost=0.41..8.44 rows=1 width=85) (actual time=0.080..0.080 rows=0 loops=1) | |
| Buffers: shared hit=4 | |
| -> Index Scan using certnames_transform_certname_key on certnames (cost=0.41..8.44 rows=1 width=85) (actual time=0.079..0.079 rows=0 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Filter: ((deactivated < $2) OR (expired < $3)) | |
| Rows Removed by Filter: 1 | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.706 ms plan: | |
| Query Text: SELECT producer_timestamp FROM factsets WHERE certname = $1 ORDER BY producer_timestamp DESC LIMIT 1 | |
| Limit (cost=8.44..8.45 rows=1 width=8) (actual time=0.311..0.312 rows=1 loops=1) | |
| Buffers: shared hit=4 | |
| -> Sort (cost=8.44..8.45 rows=1 width=8) (actual time=0.310..0.310 rows=1 loops=1) | |
| Sort Key: producer_timestamp DESC | |
| Sort Method: quicksort Memory: 25kB | |
| Buffers: shared hit=4 | |
| -> Index Scan using factsets_certname_idx on factsets (cost=0.41..8.43 rows=1 width=8) (actual time=0.072..0.075 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.667 ms plan: | |
| Query Text: select path, id, value_type_id, large_value_hash, case when large_value_hash is null and value_type_id = 5 then value end as value, case when large_value_hash is null then value_string end as value_string, value_integer, value_float, value_boolean from facts as f inner join fact_paths fp on f.fact_path_id = fp.id where f.factset_id = $1 | |
| Hash Join (cost=13.00..892.32 rows=226 width=133) (actual time=0.265..0.436 rows=119 loops=1) | |
| Hash Cond: (f.fact_path_id = fp.id) | |
| Buffers: shared hit=11 | |
| -> Bitmap Heap Scan on facts f (cost=6.18..881.83 rows=226 width=116) (actual time=0.082..0.132 rows=119 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.063..0.063 rows=160 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| -> Hash (cost=4.14..4.14 rows=214 width=23) (actual time=0.171..0.171 rows=195 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 20kB | |
| Buffers: shared hit=2 | |
| -> Seq Scan on fact_paths fp (cost=0.00..4.14 rows=214 width=23) (actual time=0.015..0.076 rows=195 loops=1) | |
| Buffers: shared hit=2 | |
| LOG: duration: 0.072 ms plan: | |
| Query Text: SELECT fs.id as factset_id, c.id as certname_id, encode(c.package_hash::bytea, 'hex') as package_hash | |
| FROM factsets fs, certnames c | |
| WHERE fs.certname = $1 AND c.certname = $2 | |
| Nested Loop (cost=0.83..16.88 rows=1 width=48) (actual time=0.067..0.068 rows=1 loops=1) | |
| Buffers: shared hit=8 | |
| -> Index Scan using factsets_certname_idx on factsets fs (cost=0.41..8.43 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Buffers: shared hit=4 | |
| -> Index Scan using certnames_transform_certname_key on certnames c (cost=0.41..8.43 rows=1 width=29) (actual time=0.019..0.020 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.198 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.197..0.197 rows=0 loops=1) | |
| Buffers: shared hit=11 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.091..0.096 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '72'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.060..0.060 rows=160 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.374 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.373..0.373 rows=0 loops=1) | |
| Buffers: shared hit=27 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.063..0.068 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '87'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.031..0.031 rows=160 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.150 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.149..0.149 rows=0 loops=1) | |
| Buffers: shared hit=27 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.062..0.070 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '119'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.030..0.030 rows=161 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.194 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.194..0.194 rows=0 loops=1) | |
| Buffers: shared hit=28 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.061..0.069 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '130'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.030..0.030 rows=162 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.148 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.147..0.147 rows=0 loops=1) | |
| Buffers: shared hit=27 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.061..0.067 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '136'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.030..0.030 rows=163 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.098 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.096..0.096 rows=0 loops=1) | |
| Buffers: shared hit=11 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.076..0.079 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '12'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.030..0.030 rows=164 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.083 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.082..0.082 rows=0 loops=1) | |
| Buffers: shared hit=11 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.068..0.070 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '36'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.030..0.030 rows=164 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.080 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.080..0.080 rows=0 loops=1) | |
| Buffers: shared hit=11 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.067..0.069 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '46'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.030..0.030 rows=164 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.090 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.090..0.090 rows=0 loops=1) | |
| Buffers: shared hit=11 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.074..0.075 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '106'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.035..0.035 rows=164 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.114 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.114..0.114 rows=0 loops=1) | |
| Buffers: shared hit=11 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.086..0.087 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '48'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.046..0.046 rows=164 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.168 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.167..0.167 rows=0 loops=1) | |
| Buffers: shared hit=27 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.071..0.073 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '27'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.031..0.031 rows=164 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 1.385 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=1.384..1.384 rows=0 loops=1) | |
| Buffers: shared hit=29 read=2 dirtied=3 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.071..0.074 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '43'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.032..0.032 rows=165 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.244 ms plan: | |
| Query Text: update facts set value_type_id = $1, large_value_hash = $2, value = $3, value_string = $4, value_integer = $5, value_float = $6, value_boolean = $7 where factset_id = $8 and fact_path_id = $9 | |
| Update on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.244..0.244 rows=0 loops=1) | |
| Buffers: shared hit=27 | |
| -> Bitmap Heap Scan on facts (cost=6.13..882.34 rows=2 width=143) (actual time=0.084..0.086 rows=1 loops=1) | |
| Recheck Cond: (factset_id = '1'::bigint) | |
| Filter: (fact_path_id = '90'::bigint) | |
| Rows Removed by Filter: 118 | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.13 rows=226 width=0) (actual time=0.038..0.038 rows=166 loops=1) | |
| Index Cond: (factset_id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.049 ms plan: | |
| Query Text: select id from environments where environment=$1 | |
| Index Scan using environments_name_key on environments (cost=0.15..8.17 rows=1 width=8) (actual time=0.041..0.041 rows=1 loops=1) | |
| Index Cond: (environment = ($1)::text) | |
| Buffers: shared hit=2 | |
| LOG: duration: 0.068 ms plan: | |
| Query Text: select id from producers where name=$1 | |
| Seq Scan on producers (cost=0.00..1.76 rows=1 width=8) (actual time=0.047..0.062 rows=1 loops=1) | |
| Filter: (name = ($1)::text) | |
| Rows Removed by Filter: 71 | |
| Buffers: shared hit=1 | |
| LOG: duration: 3.028 ms plan: | |
| Query Text: UPDATE factsets SET timestamp = $1,environment_id = $2,producer_timestamp = $3,hash = $4,producer_id = $5 WHERE id=$6 | |
| Update on factsets (cost=0.29..8.31 rows=1 width=88) (actual time=3.028..3.028 rows=0 loops=1) | |
| Buffers: shared hit=17 read=2 dirtied=1 | |
| -> Index Scan using factsets_pkey on factsets (cost=0.29..8.31 rows=1 width=88) (actual time=0.030..0.033 rows=1 loops=1) | |
| Index Cond: (id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.070 ms plan: | |
| Query Text: SELECT 1 | |
| FROM certnames | |
| WHERE certname=$1 | |
| Index Only Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=4) (actual time=0.066..0.066 rows=1 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Heap Fetches: 1 | |
| Buffers: shared hit=5 | |
| LOG: duration: 7.212 ms plan: | |
| Query Text: 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 ((fs.certname = $1) AND NOT ((fs.certname) in ( (SELECT inactive_nodes.certname AS certname FROM inactive_nodes) ) ))) | |
| Hash Join (cost=1239.63..2152.17 rows=109 width=92) (actual time=6.913..7.095 rows=104 loops=1) | |
| Hash Cond: (f.fact_path_id = fp.id) | |
| Buffers: shared hit=749 | |
| CTE inactive_nodes | |
| -> Seq Scan on certnames (cost=0.00..1224.73 rows=1 width=10) (actual time=6.551..6.551 rows=0 loops=1) | |
| Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL)) | |
| Rows Removed by Filter: 49473 | |
| Buffers: shared hit=730 | |
| -> Hash Join (cost=7.94..918.91 rows=128 width=87) (actual time=6.787..6.931 rows=119 loops=1) | |
| Hash Cond: (f.value_type_id = vt.id) | |
| Buffers: shared hit=747 | |
| -> Nested Loop (cost=6.80..916.02 rows=128 width=95) (actual time=6.750..6.860 rows=119 loops=1) | |
| Buffers: shared hit=746 | |
| -> Nested Loop Left Join (cost=0.59..16.64 rows=1 width=50) (actual time=6.700..6.707 rows=1 loops=1) | |
| Buffers: shared hit=737 | |
| -> Index Scan using factsets_certname_idx on factsets fs (cost=0.44..8.46 rows=1 width=26) (actual time=6.689..6.694 rows=1 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Filter: (NOT (hashed SubPlan 2)) | |
| Buffers: shared hit=735 | |
| SubPlan 2 | |
| -> CTE Scan on inactive_nodes (cost=0.00..0.02 rows=1 width=32) (actual time=6.552..6.552 rows=0 loops=1) | |
| Buffers: shared hit=730 | |
| -> Index Scan using environments_pkey on environments env (cost=0.15..8.17 rows=1 width=40) (actual time=0.007..0.007 rows=1 loops=1) | |
| Index Cond: (fs.environment_id = id) | |
| Buffers: shared hit=2 | |
| -> Bitmap Heap Scan on facts f (cost=6.21..897.08 rows=230 width=61) (actual time=0.043..0.116 rows=119 loops=1) | |
| Recheck Cond: (factset_id = fs.id) | |
| Heap Blocks: exact=5 | |
| Buffers: shared hit=9 | |
| -> Bitmap Index Scan on facts_factset_id_idx (cost=0.00..6.16 rows=230 width=0) (actual time=0.035..0.035 rows=167 loops=1) | |
| Index Cond: (factset_id = fs.id) | |
| Buffers: shared hit=4 | |
| -> Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.010..0.010 rows=6 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| Buffers: shared hit=1 | |
| -> Seq Scan on value_types vt (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.005 rows=6 loops=1) | |
| Buffers: shared hit=1 | |
| -> Hash (cost=4.68..4.68 rows=183 width=21) (actual time=0.106..0.106 rows=161 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 18kB | |
| Buffers: shared hit=2 | |
| -> Seq Scan on fact_paths fp (cost=0.00..4.68 rows=183 width=21) (actual time=0.013..0.050 rows=161 loops=1) | |
| Filter: (depth = 0) | |
| Rows Removed by Filter: 34 | |
| Buffers: shared hit=2 | |
| LOG: duration: 0.062 ms plan: | |
| Query Text: SELECT 1 FROM certnames WHERE certname=$1 LIMIT 1 | |
| Limit (cost=0.41..8.43 rows=1 width=4) (actual time=0.056..0.056 rows=1 loops=1) | |
| Buffers: shared hit=5 | |
| -> Index Only Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=4) (actual time=0.055..0.055 rows=1 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Heap Fetches: 1 | |
| Buffers: shared hit=5 | |
| LOG: duration: 1.636 ms plan: | |
| Query Text: UPDATE certnames SET deactivated = NULL,expired = NULL WHERE certname=$1 AND (deactivated<$2 OR expired<$3) | |
| Update on certnames (cost=0.41..8.44 rows=1 width=85) (actual time=1.635..1.635 rows=0 loops=1) | |
| Buffers: shared hit=4 | |
| -> Index Scan using certnames_transform_certname_key on certnames (cost=0.41..8.44 rows=1 width=85) (actual time=1.633..1.633 rows=0 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Filter: ((deactivated < $2) OR (expired < $3)) | |
| Rows Removed by Filter: 1 | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.197 ms plan: | |
| Query Text: select catalogs.id as catalog_id, certnames.id as certname_id, | |
| encode(catalogs.hash::bytea, 'hex') as catalog_hash, catalogs.producer_timestamp | |
| from certnames | |
| left join catalogs on catalogs.certname=certnames.certname | |
| where certnames.certname=$1 | |
| Nested Loop Left Join (cost=0.83..16.88 rows=1 width=56) (actual time=0.179..0.185 rows=1 loops=1) | |
| Join Filter: (catalogs.certname = certnames.certname) | |
| Buffers: shared hit=9 | |
| -> Index Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=18) (actual time=0.052..0.052 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Buffers: shared hit=4 | |
| -> Index Scan using catalogs_certname_idx on catalogs (cost=0.41..8.43 rows=1 width=47) (actual time=0.119..0.125 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Buffers: shared hit=5 | |
| LOG: duration: 0.018 ms plan: | |
| Query Text: select id from environments where environment=$1 | |
| Index Scan using environments_name_key on environments (cost=0.15..8.17 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1) | |
| Index Cond: (environment = ($1)::text) | |
| Buffers: shared hit=2 | |
| LOG: duration: 0.037 ms plan: | |
| Query Text: select id from producers where name=$1 | |
| Seq Scan on producers (cost=0.00..1.76 rows=1 width=8) (actual time=0.016..0.032 rows=1 loops=1) | |
| Filter: (name = ($1)::text) | |
| Rows Removed by Filter: 71 | |
| Buffers: shared hit=1 | |
| LOG: duration: 1.082 ms plan: | |
| Query Text: UPDATE catalogs SET environment_id = $1,catalog_uuid = $2,hash = $3,transaction_uuid = $4,producer_timestamp = $5,producer_id = $6,catalog_version = $7,api_version = $8,code_id = NULL,timestamp = $9,job_id = NULL WHERE id=$10 | |
| Update on catalogs (cost=0.29..8.31 rows=1 width=228) (actual time=1.080..1.080 rows=0 loops=1) | |
| Buffers: shared hit=24 read=1 dirtied=1 | |
| -> Index Scan using catalogs_pkey on catalogs (cost=0.29..8.31 rows=1 width=228) (actual time=0.053..0.056 rows=1 loops=1) | |
| Index Cond: (id = '1'::bigint) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.039 ms plan: | |
| Query Text: SELECT 1 FROM certnames WHERE certname=$1 LIMIT 1 | |
| Limit (cost=0.41..8.43 rows=1 width=4) (actual time=0.036..0.036 rows=1 loops=1) | |
| Buffers: shared hit=5 | |
| -> Index Only Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Heap Fetches: 1 | |
| Buffers: shared hit=5 | |
| LOG: duration: 0.067 ms plan: | |
| Query Text: UPDATE certnames SET deactivated = NULL,expired = NULL WHERE certname=$1 AND (deactivated<$2 OR expired<$3) | |
| Update on certnames (cost=0.41..8.44 rows=1 width=85) (actual time=0.066..0.066 rows=0 loops=1) | |
| Buffers: shared hit=4 | |
| -> Index Scan using certnames_transform_certname_key on certnames (cost=0.41..8.44 rows=1 width=85) (actual time=0.064..0.064 rows=0 loops=1) | |
| Index Cond: (certname = ($1)::text) | |
| Filter: ((deactivated < $2) OR (expired < $3)) | |
| Rows Removed by Filter: 1 | |
| Buffers: shared hit=4 | |
| LOG: duration: 30.464 ms plan: | |
| Query Text: select 1 from reports where encode(hash, 'hex'::text) = $1 limit 1 | |
| Limit (cost=0.42..8.44 rows=1 width=4) (actual time=30.461..30.461 rows=0 loops=1) | |
| Buffers: shared hit=1 read=2 | |
| -> Index Scan using reports_hash_expr_idx on reports (cost=0.42..8.44 rows=1 width=4) (actual time=30.460..30.460 rows=0 loops=1) | |
| Index Cond: (encode(hash, 'hex'::text) = 'b9a36bbf6b0dc73d61804d1a4454705fa6b67639'::text) | |
| Buffers: shared hit=1 read=2 | |
| LOG: duration: 0.045 ms plan: | |
| Query Text: select id from certnames where certname=$1 | |
| Index Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Buffers: shared hit=4 | |
| LOG: duration: 0.026 ms plan: | |
| Query Text: select id from report_statuses where status=$1 | |
| Seq Scan on report_statuses (cost=0.00..1.04 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1) | |
| Filter: (status = 'unchanged'::text) | |
| Rows Removed by Filter: 2 | |
| Buffers: shared hit=1 | |
| LOG: duration: 0.015 ms plan: | |
| Query Text: select id from environments where environment=$1 | |
| Index Scan using environments_name_key on environments (cost=0.15..8.17 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1) | |
| Index Cond: (environment = ($1)::text) | |
| Buffers: shared hit=2 | |
| LOG: duration: 0.032 ms plan: | |
| Query Text: select id from producers where name=$1 | |
| Seq Scan on producers (cost=0.00..1.76 rows=1 width=8) (actual time=0.014..0.029 rows=1 loops=1) | |
| Filter: (name = ($1)::text) | |
| Rows Removed by Filter: 71 | |
| Buffers: shared hit=1 | |
| LOG: duration: 2.013 ms plan: | |
| Query Text: INSERT INTO reports ( status_id, environment_id, catalog_uuid, receive_time, hash, transaction_uuid, puppet_version, noop, logs, report_format, start_time, producer_timestamp, cached_catalog_status, end_time, producer_id, configuration_version, code_id, noop_pending, certname, metrics, job_id ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 ) RETURNING * | |
| Insert on reports (cost=0.00..0.01 rows=1 width=1453) (actual time=1.567..1.568 rows=1 loops=1) | |
| Buffers: shared hit=33 read=2 dirtied=2 | |
| -> Result (cost=0.00..0.01 rows=1 width=1453) (actual time=0.054..0.055 rows=1 loops=1) | |
| Buffers: shared hit=1 | |
| LOG: duration: 0.499 ms plan: | |
| Query Text: UPDATE certnames SET latest_report_id = $1,latest_report_timestamp = $2 WHERE certname = $3 AND latest_report_timestamp < $4 | |
| Update on certnames (cost=0.41..8.43 rows=1 width=85) (actual time=0.295..0.295 rows=0 loops=1) | |
| Buffers: shared hit=20 | |
| -> Index Scan using certnames_transform_certname_key on certnames (cost=0.41..8.43 rows=1 width=85) (actual time=0.053..0.054 rows=1 loops=1) | |
| Index Cond: (certname = 'noway.corp.puppetlabs.net'::text) | |
| Filter: (latest_report_timestamp < '2017-06-01 16:11:02.006-07'::timestamp with time zone) | |
| Buffers: shared hit=4 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment