<commit_before># 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` rows each column in the `WHERE` or `ORDER BY` clause.

4. For each column in the `WHERE` clause, sort by the highest [cardinality](https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)) (the most unique values). This allows the database to narrow its search the fastest. Starting with the number of rows in the table and the sorted list of columns, perform [row estimation](http://www.postgresql.org/docs/current/static/row-estimation-examples.html) to see 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.<commit_msg>Updated suggested indexes guide<commit_after># 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.
{'fraction_non_alphanumeric': 0.09712230215827339, 'fraction_numerical': 0.009592326139088728, 'mean_word_length': 4.417748917748917, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 4, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7457711', 'n_tokens_mistral': 751, 'n_tokens_neox': 724, 'n_words': 324}