Automatically generate indexes

[pivotal-casebook]

We should have a rake task that generates indexes where possible.

For dynamic scopes it will be difficult to generate indexes. Perhaps there can be some sort of way to call into a dynamic scope with the options you use for a typical search and have it generate an index instead of results.

[alexhanh]

It would be nice to have some guidance, such as a wiki page, describing how to manually setup the indexes until it's automated.

[nertzy]

Agreed. I will get a wiki page up soon.

[elfassy]

+1

[ciastek]

+1

[dlikhten]

Here's an index for the multisearch table which works with the configuration:

PgSearch.multisearch_options = {
    using: {
        tsearch: {
            prefix: true
        }
    }
}

Basically everything inside the gin(...) is what is generated by pg_search, so this should go into the multi-search table and can be auto generated once the user decides how multi-search is to be globally used.

The only problem (which cannot really be auto-solved) is if the user changes configuration, they also need to change the index.

CREATE INDEX pg_multisearch_index 
ON pg_search_documents 
USING gin(to_tsvector('simple', coalesce("content"::text, '')))

[nertzy]

Thanks, @dlikhten, that looks like a reasonable index for that configuration. You should ensure that your important queries actually use the index by looking through output from EXPLAIN or EXPLAIN ANALYZE. http://www.postgresql.org/docs/current/static/sql-explain.html

For now, we have a wiki page that can help guide people to design their indexes. https://github.com/Casecommons/pg_search/wiki/Building-indexes