index for ILIKE on postgresql, inconsistent performance

Question

I have a table with about 64 Million rows, I have created the following index on the address_url column

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?

Answer 1

Different parts of the index need to examined based on what trigrams are present in the LIKE pattern, and then different parts of the table need to be examined both to recheck the pattern for false positives, and to get the other columns you selected for the true positives.

The first time for a given pattern, it needs to fetch large those parts of the index and table from disk. The consecutive second time with the same pattern, that data is already cached in memory from the previous time, and so will be much faster.

The details of this can be seen by doing explain (analyze, buffers) of the query, and especially doing so with set track_io_timing=on

If you have enough RAM to cache both the entire index and entire table into RAM, then you could use pg_prewarm to get them all into memory up front so you don't have to do it piecemeal.

Answer 2

To accelerate 'like' queries, you can use a varchar_pattern_ops (or text_pattern_ops) index.

Here is the syntax:

CREATE INDEX address_link_address_url_like
    ON public.address_link (address_url varchar_pattern_ops);

You' might find this documentation page usefull.