puppetlabs/puppetdb — pull request #1061 by wkalt (2014-08-21 00:56:05+00:00)
Title: (PDB-810) WIP  Load test structured facts indices.

This patch addresses some performance issues for structured facts

- deletes the index of fact_values value_integer and value_float. These
indices were dead weight since a coalesce causes them to be disregarded.
This is a stopgap solution to be augmented by PDB-811

- creates a trgm index on fact_values column value_string, so that our trgm
indexes are now on fact_paths and value_string. This greatly speeds
up queries on value_string and only moderately affects the table size. See
https://gist.githubusercontent.com/wkalt/42792c53a5dc9f0840c4/raw/29f261316bc7e39d888250401f0b1f1c8c28a87b/gistfile1.txt
for some comparisons on a 2000 node dataset with 100 facts per node.

- put a DISTINCT clause in our garbage collection on fact_paths. This incurs a
minor performance cost in small datasets, but yields a tremendous benefit in
large and highly duplicated sets by reducing the load on the accompanying IN.
(see https://gist.github.com/wkalt/09225b71588c35674827)

* still need to confirm that the depth index is used.