I have a table with about 64 Million rows, I have created the following index on the
CREATE INDEX trgm_idx_address_link ON public.address_link USING gin (address_url COLLATE pg_catalog."default" gin_trgm_ops) TABLESPACE pg_default;
The server is not under any load right now. And I am testing the performance of like.
Let's say we have the following query
SELECT * FROM "address_link" WHERE "address_url" ILIKE '%eden-hill%' LIMIT 1
Sometimes if I run that for the first time, it can take 10 seconds. If I run it again, it would complete in 0.195 seconds
I then created a btree index on the same column to see if would help
CREATE INDEX address_link_address_url ON public.address_link USING btree (address_url COLLATE pg_catalog."default") TABLESPACE pg_default;
But it did not help much. Sometimes the first time I search for a keyword it takes 2 seconds, the second time takes 0.02 seconds. Is there anything I can do to make the performance more consistent?