Postgresql – How to create pg_trgm compound indexes with date columns

gin-indexindexpostgresql

SELECT col1, max(date1) as max_date
FROM table
WHERE col1 ILIKE 'name'
GROUP BY col1

TYPES:Here col1 is varchar and date1 is timestamp with time zone data type. So created extension CREATE EXTENSION pg_trgm

Then tried the following indexes and got the errors:

1: Issue: ERROR: operator class "gin_trgm_ops" does not accept data type timestamp with time zone

CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
ON table 
USING gin (provider_id, date1 gin_trgm_ops);

2: Issue: ERROR: operator class "text_pattern_ops" does not exist for access method "gin"

 CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
    ON table 
    USING gin (provider_id, date1 text_pattern_ops);

How can I create an index for the above query for faster execution? Any help will be really appreciated!

EDIT:
EXPLAIN output of current query

Best Answer

Please pick identifiers for your example queries which are not reserved words.

The pg_trgm module only supports text-like columns. Your date column is presumably a date type (or a timestamp, or something like that), not text-like.

To do what you want, you first need to allow date-like data to be indexed in a gin index. For example, by create extension btree_gin.

Then you can create your index:

create index on bar using gin (col1 gin_trgm_ops, date_column);

You don't need to provide the operator class for the date_column, because btree_gin provides default classes. The operator class you do provide needs to be next to the column it applies to. They are per-column, not per-index.

Note that while this answers your question, it probably doesn't solve your problem. The above index is legal and can be built, but it will not speed up your query (beyond what a pg_trgm indx on just col1 does). If you want help making the query run faster, you should show us the EXPLAIN (ANALYZE) of the existing query, as well as other information.

Related Question