From: | Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru> |
---|---|
To: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: tgrm index for word_similarity |
Date: | 2017-10-22 19:58:33 |
Message-ID: | 20171022195832.GB2004@arthur.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-10-22 20:48:19 | Re: parray_gin and \d errors in PG10 |
Previous Message | Arthur Zakirov | 2017-10-22 19:15:31 | Re: How to get login user name and host name in pgaudit |