On Sat, Oct 21, 2017 at 10:01:56PM -0700, Igal @ Lucee.org wrote: > > 1) I thought that the whole idea behind indexes on expressions is that the > index would be used in a WHERE clause? See > https://www.postgresql.org/docs/10/static/indexes-expressional.html - Am I > missing something? > I think the idea is a little bit different. It is about computing index entries only once, during index creation. During scan PostgreSQL doesn't compute such entries every time. I am not very good at PostgreSQL's planner. But I know that PostgreSQL uses index scan for pg_trgm only with %, <%, ~~, ~~*, ~, ~* operators. pg_trgm's operator classes (which should be implemented for index scan) are designed in this way. > 2) A query with `WHERE input <% name` utilizes the index, but a query > without a WHERE clause at all does not? Because sequential scan is cheaper here than index scan. > > 3) What happens if I do not create an index at all? Does the query that I > run in 30 - 40ms, the one that does not utilize an index, creates all of the > tri-grams on the fly each time that it runs? Would it be possible for me to > create a TABLE or a VIEW with the tri-grams so that there is no need to > create them each time the query runs? > As far as I know you can't do it nowadays. You can't create an trigram column, as you can do it for FTS, you can create an tsvector column. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
