I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time.
select email from customer where lower(email) like '%gmail'; CREATE INDEX idx1 ON customer USING gin (lower(email) gin_trgm_ops);. Index created as "idx1" gin (lower(email::text) gin_trgm_ops)
explain plan gives as below
### Bitmap Heap Scan on customer (cost=168.51..367.36 rows=194 width=22) (actual time=381.844..1807.673 rows=931488 loops=1) Recheck Cond: (lower((email)::text) ~~ '%gmail.com'::text) Rows Removed by Index Recheck: 556 Heap Blocks: exact=53254 -> Bitmap Index Scan on idx1 (cost=0.00..168.46 rows=194 width=0) (actual time=372.263..372.263 rows=933383 loops=1) Index Cond: (lower((email)::text) ~~ '%gmail.com'::text) Planning time: 0.741 ms Execution time: 1988.646 ms (8 rows)
Please let me know if this can be fine-tuned further