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!
citext
or lower(text) are. You would need to show the new index and new query, as well asEXPLAIN ANALYZE
of it. (The ANALYZE part is important, not just EXPLAIN). It might be better to post it as a new question, as this is rather far afield of your original title now. You could simplify it even further by just doing a regular (case-sensitive) equality first, and worry about insensitive one you get the simpler one working.