Skip to content

Instantly share code, notes, and snippets.

@wkalt

wkalt/gist:275db03411560f047e38

Created August 20, 2014 00:18
Show Gist options
  • Save wkalt/275db03411560f047e38 to your computer and use it in GitHub Desktop.
Save wkalt/275db03411560f047e38 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
WITHOUT INDEXES WITH TRGM INDEXES:
database size database size
schema | name | size | index | ratio | total schema | name | size | index | ratio | total
-------------------+------------------------------------+------------+---------+-------+--------- -------------------+------------------------------------+------------+---------+-------+---------
public | fact_values | 21 MB | 30 MB | 60% | 51 MB public | fact_paths | 15 MB | 67 MB | 83% | 82 MB
public | fact_paths | 15 MB | 31 MB | 69% | 46 MB public | fact_values | 21 MB | 39 MB | 66% | 60 MB
public | fact_values_path_id_value_key | 0 bytes | 18 MB | 100% | 18 MB public | fact_paths_path_trgm | 0 bytes | 24 MB | 100% | 24 MB
public | facts | 7136 kB | 6544 kB | 48% | 13 MB public | fact_values_path_id_value_key | 0 bytes | 18 MB | 100% | 18 MB
public | fact_paths_path_type_id_key | 0 bytes | 12 MB | 100% | 12 MB pg_catalog | pg_statistic | 12 MB | 4056 kB | 26% | 16 MB
public | fact_paths_name | 0 bytes | 6608 kB | 100% | 6608 kB public | facts | 7072 kB | 6456 kB | 48% | 13 MB
public | facts_factset_id_fact_value_id_key | 0 bytes | 6512 kB | 100% | 6512 kB public | fact_paths_name_trgm | 0 bytes | 12 MB | 100% | 12 MB
public | fact_paths_depth | 0 bytes | 4664 kB | 100% | 4664 kB public | fact_paths_path_type_id_key | 0 bytes | 12 MB | 100% | 12 MB
public | fact_values_value_float_idx | 0 bytes | 4632 kB | 100% | 4632 kB public | fact_values_string_trgm | 0 bytes | 8952 kB | 100% | 8952 kB
public | fact_values_value_integer_idx | 0 bytes | 4616 kB | 100% | 4616 kB public | fact_paths_name | 0 bytes | 6648 kB | 100% | 6648 kB
public | fact_paths_value_type_id | 0 bytes | 4568 kB | 100% | 4568 kB public | facts_factset_id_fact_value_id_key | 0 bytes | 6424 kB | 100% | 6424 kB
public | fact_paths_pkey | 0 bytes | 3640 kB | 100% | 3640 kB public | fact_values_value_float_idx | 0 bytes | 4680 kB | 100% | 4680 kB
public | fact_values_pkey | 0 bytes | 3640 kB | 100% | 3640 kB public | fact_paths_depth | 0 bytes | 4632 kB | 100% | 4632 kB
pg_catalog | pg_depend | 408 kB | 544 kB | 58% | 952 kB public | fact_values_value_integer_idx | 0 bytes | 4584 kB | 100% | 4584 kB
pg_catalog | pg_proc | 504 kB | 312 kB | 39% | 816 kB public | fact_paths_value_type_id | 0 bytes | 4544 kB | 100% | 4544 kB
pg_catalog | pg_attribute | 464 kB | 336 kB | 42% | 800 kB public | fact_values_pkey | 0 bytes | 3608 kB | 100% | 3608 kB
pg_catalog | pg_rewrite | 88 kB | 448 kB | 84% | 536 kB public | fact_paths_pkey | 0 bytes | 3608 kB | 100% | 3608 kB
pg_catalog | pg_statistic | 240 kB | 208 kB | 47% | 448 kB pg_toast | pg_toast_2619 | 2952 kB | 88 kB | 3% | 3040 kB
pg_catalog | pg_description | 248 kB | 168 kB | 41% | 416 kB pg_catalog | pg_statistic_relid_att_inh_index | 0 bytes | 984 kB | 100% | 984 kB
pg_toast | pg_toast_2618 | 336 kB | 48 kB | 13% | 384 kB pg_catalog | pg_depend | 408 kB | 552 kB | 58% | 960 kB
pg_catalog | pg_depend_reference_index | 0 bytes | 264 kB | 100% | 264 kB pg_catalog | pg_proc | 504 kB | 312 kB | 39% | 816 kB
pg_catalog | pg_shdepend | 8192 bytes | 240 kB | 97% | 248 kB pg_catalog | pg_attribute | 464 kB | 336 kB | 42% | 800 kB
pg_catalog | pg_depend_depender_index | 0 bytes | 248 kB | 100% | 248 kB pg_catalog | pg_rewrite | 88 kB | 448 kB | 84% | 536 kB
pg_catalog | pg_class | 128 kB | 112 kB | 47% | 240 kB pg_catalog | pg_description | 248 kB | 168 kB | 41% | 416 kB
pg_catalog | pg_operator | 112 kB | 112 kB | 50% | 224 kB pg_toast | pg_toast_2618 | 336 kB | 48 kB | 13% | 384 kB
pg_catalog | pg_proc_proname_args_nsp_index | 0 bytes | 200 kB | 100% | 200 kB pg_catalog | pg_depend_reference_index | 0 bytes | 272 kB | 100% | 272 kB
pg_catalog | pg_shdepend_depender_index | 168 kB | 24 kB | 13% | 192 kB pg_catalog | pg_shdepend | 8192 bytes | 240 kB | 97% | 248 kB
pg_catalog | pg_type | 80 kB | 104 kB | 57% | 184 kB pg_catalog | pg_depend_depender_index | 0 bytes | 248 kB | 100% | 248 kB
pg_catalog | pg_attribute_relid_attnam_index | 152 kB | 24 kB | 14% | 176 kB pg_catalog | pg_class | 128 kB | 112 kB | 47% | 240 kB
pg_catalog | pg_amop | 32 kB | 128 kB | 80% | 160 kB pg_catalog | pg_operator | 112 kB | 112 kB | 50% | 224 kB
pg_catalog | pg_collation | 72 kB | 80 kB | 53% | 152 kB pg_catalog | pg_proc_proname_args_nsp_index | 0 bytes | 200 kB | 100% | 200 kB
pg_toast | pg_toast_2619 | 88 kB | 48 kB | 36% | 136 kB pg_catalog | pg_shdepend_depender_index | 168 kB | 24 kB | 13% | 192 kB
pg_catalog | pg_description_o_c_o_index | 0 bytes | 128 kB | 100% | 128 kB pg_catalog | pg_type | 80 kB | 104 kB | 57% | 184 kB
pg_catalog | pg_attribute_relid_attnum_index | 104 kB | 24 kB | 19% | 128 kB pg_catalog | pg_attribute_relid_attnam_index | 152 kB | 24 kB | 14% | 176 kB
pg_catalog | pg_constraint | 24 kB | 104 kB | 82% | 128 kB pg_catalog | pg_amop | 32 kB | 128 kB | 80% | 160 kB
pg_catalog | pg_trigger | 24 kB | 88 kB | 79% | 112 kB pg_catalog | pg_collation | 72 kB | 80 kB | 53% | 152 kB
pg_catalog | pg_conversion | 24 kB | 80 kB | 77% | 104 kB pg_catalog | pg_attribute_relid_attnum_index | 104 kB | 24 kB | 19% | 128 kB
pg_catalog | pg_amproc | 24 kB | 80 kB | 77% | 104 kB pg_catalog | pg_constraint | 24 kB | 104 kB | 82% | 128 kB
pg_catalog | pg_index | 40 kB | 64 kB | 62% | 104 kB pg_catalog | pg_description_o_c_o_index | 0 bytes | 128 kB | 100% | 128 kB
information_schema | sql_features | 56 kB | 40 kB | 42% | 96 kB pg_catalog | pg_trigger | 24 kB | 88 kB | 79% | 112 kB
pg_catalog | pg_cast | 16 kB | 64 kB | 80% | 80 kB pg_catalog | pg_conversion | 24 kB | 80 kB | 77% | 104 kB
public | factsets | 8192 bytes | 72 kB | 90% | 80 kB pg_catalog | pg_amproc | 24 kB | 80 kB | 77% | 104 kB
public | environments | 8192 bytes | 72 kB | 90% | 80 kB pg_catalog | pg_index | 40 kB | 64 kB | 62% | 104 kB
pg_catalog | pg_attrdef | 8192 bytes | 72 kB | 90% | 80 kB information_schema | sql_features | 56 kB | 40 kB | 42% | 96 kB
pg_catalog | pg_opfamily | 16 kB | 64 kB | 80% | 80 kB pg_catalog | pg_cast | 16 kB | 64 kB | 80% | 80 kB
pg_catalog | pg_opclass | 16 kB | 64 kB | 80% | 80 kB public | factsets | 8192 bytes | 72 kB | 90% | 80 kB
pg_catalog | pg_ts_config_map | 16 kB | 64 kB | 80% | 80 kB public | environments | 8192 bytes | 72 kB | 90% | 80 kB
pg_catalog | pg_tablespace | 8192 bytes | 64 kB | 89% | 72 kB pg_catalog | pg_attrdef | 8192 bytes | 72 kB | 90% | 80 kB
pg_catalog | pg_namespace | 8192 bytes | 64 kB | 89% | 72 kB pg_catalog | pg_opclass | 16 kB | 64 kB | 80% | 80 kB
public | resource_events | 0 bytes | 72 kB | 100% | 72 kB pg_catalog | pg_ts_config_map | 16 kB | 64 kB | 80% | 80 kB
fact name query (ms) (name ~ 'abd') fact name query
103.777 74.410
102.637 73.974
101.263 74.489
128.759 75.096
98.212 73.482
105.868 73.888
100.810 73.407
100.423 74.469
110.230 73.806
103.172 75.552
98.718 78.740
101.150 74.826
101.222 73.159
104.567 74.908
99.630 75.265
100.022 74.423
102.272 86.357
100.265 93.742
108.480 86.224
114.369 84.193
mean: 104.29 mean: 77.22 25.95 % reduction
fact node path query (path ~ '^(?:((?!\#\~).)+|.?)#~(?:((?!\#\~).)+|.?)#~a(?:((?!\#\~).)+|.?)$') fact node path query
2334.798 345.927
2372.902 344.895
2335.306 348.444
2367.367 341.779
2482.731 382.090
2430.773 341.720
2375.452 346.305
2375.569 380.796
2365.161 378.665
2474.419 379.698
2368.496 341.107
2412.900 342.837
2398.471 380.169
2454.287 340.000
2426.770 343.224
2390.139 379.226
2396.801 378.481
2409.961 342.626
2409.839 354.901
2392.480 355.071
mean: 2398.731 mean: 357.3981 85.11 % reduction
fact value string query (value_string ~ '^networking#~eth.*#~macaddresses#~.*$') fact value string query
84.588 4.408
86.162 4.465
83.028 4.389
88.742 4.446
86.520 4.533
83.807 4.449
86.037 4.464
84.236 4.482
82.806 4.447
83.610 4.560
85.528 4.472
84.135 4.438
87.544 4.501
85.648 4.572
86.327 4.504
84.733 4.490
87.764 4.622
88.831 4.561
93.404 4.615
85.463 4.389
mean: 85.94 mean: 4.49 94.7% reduction
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment