Sha256: 40bd18b94d3d38ad3d008039601316750552ab8bdd79027b70a8f510373645d8

Contents?: true

Size: 1.18 KB

Versions: 22

Compression:

Stored size: 1.18 KB

Contents

# How PgHero Suggests Indexes

1. Get the most time-consuming queries from [pg_stat_statements](http://www.postgresql.org/docs/9.3/static/pgstatstatements.html).

2. Parse queries with [pg_query](https://github.com/lfittl/pg_query).  Look for a single table with a `WHERE` clause that consists of only `=`, `IN`, `IS NULL` or `IS NOT NULL` and/or an `ORDER BY` clause.

3. Use the [pg_stats](http://www.postgresql.org/docs/current/static/view-pg-stats.html) view to get estimates about distinct rows and percent of `NULL` values for each column.

4. For each column in the `WHERE` clause, sort by the highest [cardinality](https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)) (most unique values). This allows the database to narrow its search the fastest. Perform [row estimation](http://www.postgresql.org/docs/current/static/row-estimation-examples.html) to get the expected number of rows as we add columns to the index.

5. Continue this process with columns in the `ORDER BY` clause.

6. To make sure we don’t add useless columns, stop once we narrow it down to 50 rows in steps 5 or 6. Also, recheck the last columns to make sure they add value.

7. Profit :moneybag:

## TODO

- examples

Version data entries

22 entries across 22 versions & 1 rubygems

Version Path
pghero-1.7.0 guides/Suggested-Indexes.md
pghero-1.6.5 guides/Suggested-Indexes.md
pghero-1.6.4 guides/Suggested-Indexes.md
pghero-1.6.3 guides/Suggested-Indexes.md
pghero-1.6.2 guides/Suggested-Indexes.md
pghero-1.6.1 guides/Suggested-Indexes.md
pghero-1.6.0 guides/Suggested-Indexes.md
pghero-1.5.3 guides/Suggested-Indexes.md
pghero-1.5.2 guides/Suggested-Indexes.md
pghero-1.5.1 guides/Suggested-Indexes.md
pghero-1.5.0 guides/Suggested-Indexes.md
pghero-1.4.2 guides/Suggested-Indexes.md
pghero-1.4.1 guides/Suggested-Indexes.md
pghero-1.4.0 guides/Suggested-Indexes.md
pghero-1.3.2 guides/Suggested-Indexes.md
pghero-1.3.1 guides/Suggested-Indexes.md
pghero-1.3.0 guides/Suggested-Indexes.md
pghero-1.2.4 guides/Suggested-Indexes.md
pghero-1.2.3 guides/Suggested-Indexes.md
pghero-1.2.2 guides/Suggested-Indexes.md