gistfile1.txt

postgres=# explain analyze SELECT 1 FROM words WHERE word LIKE '%xxxprivate.co.za' AND reverse(word) LIKE reverse('xxxprivate.co.za%') limit 1;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..23300.06 rows=1 width=0) (actual time=581.073..581.073 rows=1 loops=1)
   ->  Seq Scan on words  (cost=0.00..23300.06 rows=1 width=0) (actual time=581.070..581.070 rows=1 loops=1)
         Filter: ((word ~~ '%xxxprivate.co.za'::text) AND (reverse(word) ~~ '%az.oc.etavirpxxx'::text))
 Total runtime: 581.522 ms
(4 rows)

postgres=# explain analyze SELECT 1 FROM words WHERE reverse(word) LIKE reverse('%xxxprivate.co.za') limit 1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.01..0.09 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
   ->  Index Scan using words_reverse_idx on words  (cost=0.01..8.51 rows=99 width=0) (actual time=0.040..0.040 rows=1 loops=1)
         Index Cond: ((reverse(word) ~>=~ 'az.oc.etavirpxxx'::text) AND (reverse(word) ~<~ 'az.oc.etavirpxxy'::text))
         Filter: (reverse(word) ~~ 'az.oc.etavirpxxx%'::text)
 Total runtime: 0.112 ms
(5 rows)