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

Here col1 is varchar and date 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, date 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, date text_pattern_ops);

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

EDIT: So this works syntactically but does not speed up the query: 

CREATE INDEX CONCURRENTLY trgm_provider_date_index
ON table 
USING gin (provider_id gin_trgm_ops, date_var)
Braiam
  • 1
  • 11
  • 47
  • 78
Atihska
  • 4,803
  • 10
  • 56
  • 98

1 Answers1

1

As far as I know, Postgres is not able to optimise min()/max() queries using a GIN index.

You can add date columns to a GIN index (as well as other scalar types, like int, text, etc.) by installing the btree_gin contrib module. However, because GIN indexing methods are under no obligation to store their entries in any particular order, Postgres can't assume that they'll be useful for finding the maximum, so they'll be ignored.

(I can't find a specific reference to this in the docs, but I believe that it's identical to the limitation which prevents anything other than a B-tree from being used to optimise an ORDER BY.)

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
  • Please check my edit. Creating index like this works but did not speed up the query. `CREATE INDEX CONCURRENTLY trgm_general_provider_date_index ON general_catalog USING gin (provider_id gin_trgm_ops, acquisition_date)` – Atihska Jan 24 '18 at 18:35
  • @Atihska: Like I said, I wouldn't expect it to help, if you're comparing it to an index on `provider_id` alone (if anything, the additional column is going to make it worse). – Nick Barnes Jan 24 '18 at 22:32
  • So just create index on `provider_id`? How can I make it faster? – Atihska Jan 24 '18 at 23:09
  • @Atihska: "How can I make it faster" is a bit too broad to address in comments. You should probably post a new question. Be sure to include table and index definitions, sample data, the problematic query, and your `EXPLAIN ANALYSE` output. – Nick Barnes Jan 25 '18 at 07:28