example.txt

[local]:trgm_test=# create extension pg_trgm;
CREATE EXTENSION
Time: 12.176 ms
[local]:trgm_test=# create table trgm_test(t text);
CREATE TABLE
Time: 9.621 ms
[local]:trgm_test=# create index on trgm_test using gist(t gist_trgm_ops);
CREATE INDEX
Time: 6.714 ms
[local]:trgm_test=# insert into trgm_test(t) select 'Ouster' from generate_series(1, 1000000);
INSERT 0 1000000
Time: 3658.297 ms (00:03.658)
[local]:trgm_test=# insert into trgm_test(t) values ('ouster');
INSERT 0 1
Time: 5.880 ms
[local]:trgm_test=# explain (analyze, buffers) select t from trgm_test where t ~ 'ouster' limit 25;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..8.43 rows=1 width=7) (actual time=381.926..507.526 rows=1 loops=1)
   Buffers: shared hit=397109
   ->  Index Scan using trgm_test_t_idx on trgm_test  (cost=0.41..8.43 rows=1 width=7) (actual time=381.924..507.523 rows=1 loops=1)
         Index Cond: (t ~ 'ouster'::text)
         Rows Removed by Index Recheck: 1000000
         Buffers: shared hit=397109
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.183 ms
 Execution Time: 507.553 ms
(10 rows)

Time: 508.064 ms
[local]:trgm_test=# explain (analyze, buffers) select t from trgm_test where t ~ 'Ouster' limit 25;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.42 rows=25 width=7) (actual time=0.012..0.039 rows=25 loops=1)
   Buffers: shared hit=1
   ->  Seq Scan on trgm_test  (cost=0.00..16925.01 rows=1000001 width=7) (actual time=0.011..0.036 rows=25 loops=1)
         Filter: (t ~ 'Ouster'::text)
         Buffers: shared hit=1
 Planning Time: 0.090 ms
 Execution Time: 0.051 ms
(7 rows)

Time: 0.459 ms