Skip to content

Instantly share code, notes, and snippets.

View wkalt's full-sized avatar

Wyatt Alt wkalt

15 followers · 1 following
View GitHub Profile
@wkalt
wkalt / gist:dc5d04ebf6cb4ff4f823
Created July 29, 2014 00:03
[~/Documents/puppetdb] (ticket/master/pdb-772-change-facts) $ curl -X GET http://localhost:8080/v4/fact-nodes --data-urlencode 'query=["=","name","my_structured_fact"]'
[ {
"value" : {
"f" : "",
"b" : {
"g" : "",
"e" : [ 3.14, 2.71 ],
"d" : "bar",
"c" : [ 1, 2, 3 ]
},
@wkalt
wkalt / gist:ddbc8243d1874ceff73c
Created August 5, 2014 22:54
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=157.17..158.98 rows=6 width=209) (actual time=2.153..2.282 rows=115 loops=1)
Hash Cond: (fs.certname = certnames.name)
-> Sort (cost=104.79..105.08 rows=115 width=219) (actual time=2.076..2.102 rows=115 loops=1)
Sort Key: fp.name, fs.certname
Sort Method: quicksort Memory: 42kB
-> Nested Loop Left Join (cost=62.98..100.86 rows=115 width=219) (actual time=0.335..1.218 rows=115 loops=1)
-> Hash Join (cost=62.83..72.30 rows=115 width=195) (actual time=0.318..0.786 rows=115 loops=1)
Hash Cond: (fv.value_type_id = vt.id)
@wkalt
wkalt / gist:1217275edc63054cc880
Created August 8, 2014 15:22
import urllib2
import urllib
import json
query = '["or",\
["and", ["=", "type", "Class"], ["=", "title", "Php"]],\
["and", ["=", "type", "Class"], ["=", "title", "Nginx"]]]'
url = 'http://localhost:8080/v4/resources?%s' % urllib.urlencode({'query': query})
data = json.loads(urllib2.urlopen(url).read())
@wkalt
wkalt / gist:29c97f8e8c704e8890d6
Created August 18, 2014 17:39
MASTER
curl -X GET http://localhost:8080/v4/facts/trusted
[ {
"value" : {
"authenticated" : "remote",
"certname" : "mbp.local"
},
"name" : "trusted",
"environment" : "production",
@wkalt
wkalt / gist:76a62e4c4db1d26b1eef
Created August 18, 2014 19:25
curl -X GET http://localhost:8080/v4/fact-nodes --data-urlencode 'query=["=",["name","certname"],["trusted","mbp.local"]]'
[ {
"certname" : "mbp.local",
"path" : [ "trusted", "certname" ],
"name" : "trusted",
"value" : "mbp.local",
"environment" : "production"
}, {
"certname" : "mbp.local",
"path" : [ "trusted", "authenticated" ],
@wkalt
wkalt / gist:01bbfa55b1434b0ac546
Created August 18, 2014 19:36
curl -X GET http://localhost:8080/v4/facts --data-urlencode 'query=["in", "name", ["extract", "name", ["select-fact-nodes",["=",["name","certname"],["trusted","mbp.local"]]]]]'
[ {
"value" : {
"authenticated" : "remote",
"certname" : "mbp.local"
},
"name" : "trusted",
"environment" : "production",
"certname" : "mbp.local"
@wkalt
wkalt / gist:2b0089509b3a36cc8332
Created August 19, 2014 19:33
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=45.17..818.21 rows=3 width=85) (actual time=16.043..22.796 rows=2 loops=1)
-> Nested Loop Left Join (cost=44.75..800.09 rows=3 width=85) (actual time=16.019..22.741 rows=2 loops=1)
Join Filter: (fs.environment_id = env.id)
Rows Removed by Join Filter: 6
-> Nested Loop (cost=44.75..798.86 rows=3 width=89) (actual time=16.007..22.724 rows=2 loops=1)
Join Filter: (fp.value_type_id = vt.id)
Rows Removed by Join Filter: 10
-> Seq Scan on value_types vt (cost=0.00..1.06 rows=6 width=14) (actual time=0.003..0.005 rows=6 loops=1)
@wkalt
wkalt / gist:699c3cbd1571c004109b
Created August 19, 2014 23:47
wyatt=# select * from pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | query
---------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+---------------------------------------------------------------------------
2095234 | puppetdb | 6475 | 65968 | puppetdb | | 127.0.0.1 | | 51033 | 2014-08-19 16:21:16.738214-07 | <<NULL>> | 2014-08-19 16:21:27.787003-07 | 2014-08-19 16:21:27.797803-07 | f | idle | COMMIT
2095234 | puppetdb | 6476 | 65968 | puppetdb | | 127
@wkalt
wkalt / gist:275db03411560f047e38
Created August 20, 2014 00:18
WITHOUT INDEXES WITH TRGM INDEXES:
database size database size
schema | name | size | index | ratio | total schema | name | size | index | ratio | total
-------------------+------------------------------------+------------+---------+-------+--------- -------------------+------------------------------------+------------+---------+-------+---------
public | fact_values |
@wkalt
wkalt / gist:b229632a403ae2efc260
Created August 20, 2014 16:18
EXPLAIN ANALYZE
delete from fact_values where ID NOT IN (SELECT fact_value_id FROM facts);
EXPLAIN ANALYZE
delete from fact_paths where ID NOT IN (SELECT path_id FROM fact_values);
EXPLAIN ANALYZE
delete from resource_params_cache where NOT EXISTS (SELECT * FROM catalog_resources cr WHERE cr.resource=resource_params_cache.resource);
EXPLAIN ANALYZE