1
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

  • Please don't post text as images. – jjanes Jan 25 '18 at 4:40
  • Your example of DIGITAL_GLOBE doesn't have any wildcards in it. Is this just a poor example, or is that really how your queries will be? Using pg_trgm to achieve case-insensitive equality is not going to be very efficient. – jjanes Jan 25 '18 at 4:46
  • @jjanes: Thank you for your reply. Oh so we don't use pg_trgm for case-insensitive equality? I am purely using it for this purpose. I tried adding index for LOWER(text) and use it in a query but that is also not helping. Any suggestions will be helpful here. Thanks! – Atihska Jan 25 '18 at 6:31
  • pg_trgm will work for that purpose, it just won't be nearly as efficient as citext or lower(text) are. You would need to show the new index and new query, as well as EXPLAIN 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. – jjanes Jan 25 '18 at 6:55
2

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.

  • Thanks @jjanes. Please see my edits for EXPLAIN output that I added. Let me know how can I speed up this query. col1 is provider_id here. – Atihska Jan 23 '18 at 21:52
  • I created the index and when I used EXPLAIN, it's not using the indexes. Could you please help me here? – Atihska Jan 24 '18 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.