PREPARE statement decides to use no index after a few calls even thought it's slower

I have created an index for Title for products.

CREATE INDEX products_title_trgm_idx 
ON products USING gin(title gin_trgm_ops)

I try to create a prepare statement and when i execute multiple calls on it with the same search value it uses my index for the first 4 queries and it's super fast around 20 ms. But on the fifth time it just randomly decides not to use the index which makes it execute 3-4s. Why is that happening?

PREPARE my_test AS SELECT "products"."id","products"."active","products"."title","products"."subtitle","products"."description","products"."isbn","products"."ean","products"."bznr","products"."cover_p
icture","products"."publication_date","products"."edition","products"."publisher","products"."stock","products"."delivery_time","products"."selling_price","products"."width","products"."height","products"."lengt
h","products"."weight" FROM "products" WHERE products.title ILIKE $1 LIMIT 10;

I created this prepare statement then did

EXECUTE my_test('%Warum Frauen alles besser wissen - und trotzdem alles falsch machen%'); 

5 times in a row, first 4 times it was using an index and was super fast, but on the fifth time and the times after that it uses seq scan which is super slow? Why is it doing that? I tried disabling seq scan and it would go with the index and execute it in 20 ms always, but I know that's not a good idea to disable.

In your case, PostgreSQL switches to a generic plan after the fifth execution of the statement. Usually that is a good move, because it saves the overhead of planning the statement every time it is executed. It just happens to go wrong in your case.

After the first five executions, PostgreSQL tests if the generic plan promises to be as efficient as the custom plans executed so far. That seems to go wrong in your case. So either PostgreSQL estimates the custom plans too expensive, or it estimates the generic plan too cheap.

Without seeing the EXPLAIN output for both plans, I have to guess (from PostgreSQL v16 on, you can get the generic plan with EXPLAIN (GENERIC_PLAN)). My guess is that the slow plan uses a parallel sequential scan. By default, PostgreSQL estimates index scans as fairly expensive, because random_page_cost is four times as high as seq_page_cost. If you don't have spinning disks, or if your data are cached, you should reduce random_page_cost to tell PostgreSQL. That may be enough to make PostgreSQL prefer the index scan. Other possible measures could be to disable parallel query by setting max_parallel_workers_per_gather to 0.

Finally, there is a switch to disable the generation of generic plans altogether: you can set plan_cache_mode to force_custom_plan. Try that if all else fails.