Postgres sometimes uses sequential scan instead of an index only scan for data that doesn't exist

We are using Postgres 12 and We have a simple table like this:

> d live_event_segmentation
                  Table "public.live_event_segmentation"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 login_id   | bigint                      |           | not null | 
 event_id   | text                        |           | not null | 
 expires_at | timestamp without time zone |           |          | 
Indexes:
    "live_event_segmentation_pkey" PRIMARY KEY, btree (login_id, event_id)
    "live_event_segmentations_event_id_idx" btree (event_id)

Size of the table is:

> dt+ live_event_segmentation
                              List of relations
 Schema |          Name           | Type  |   Owner    |  Size  | Description 
--------+-------------------------+-------+------------+--------+-------------
 public | live_event_segmentation | table | liveevents | 171 MB | 
(1 row)

and the whole DB fits into RAM.

This table has this distribution of event_id:

> select event_id, count(*) from live_event_segmentation group by 1;
        event_id        | count  
------------------------+--------
 euro2020Test           |     67
 fevent20               | 164310
 summer2020Test         |      9
(3 rows)

And our app is executing this query a few times a second:

explain analyze select 1 as "one" where exists (select 1 as "one" from "public"."live_event_segmentation" where "public"."live_event_segmentation"."event_id" = 'summer2020' limit 1);
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=2.44..2.45 rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)
   One-Time Filter: $0
   InitPlan 1 (returns $0)
     ->  Index Only Scan using live_event_segmentations_event_id_idx on live_event_segmentation  (cost=0.42..2.44 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)
           Index Cond: (event_id = 'summer2020'::text)
           Heap Fetches: 0
 Planning Time: 0.106 ms
 Execution Time: 0.040 ms
(8 rows)

When we run this query from psql it always uses an index only scan, but when our Java app runs this same query it often uses a sequential scan. We saw this from the logs by using auto_explain extension and setting log_min_duration_statement=20 (so that we can see query parameters which auto_explain doesn’t display). The only difference between psql and our app is that the app uses a prepared statement, so when we tried it in psql sometimes it would actually use a sequential scan:

> prepare p1(text, int) AS select 1 as "one" where exists (select 1 as "one" from "public"."live_event_segmentation" where "public"."live_event_segmentation"."event_id" = $1 limit $2);
PREPARE
> explain analyze execute p1('summer2020', 1);
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.29..0.30 rows=1 width=4) (actual time=67.093..67.093 rows=0 loops=1)
   One-Time Filter: $0
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..2396.72 rows=8129 width=4) (actual time=67.090..67.090 rows=0 loops=1)
           ->  Seq Scan on live_event_segmentation  (cost=0.00..23968.34 rows=81294 width=4) (actual time=67.087..67.087 rows=0 loops=1)
                 Filter: (event_id = $1)
                 Rows Removed by Filter: 163728
 Planning Time: 0.138 ms
 Execution Time: 67.171 ms
(9 rows)

What’s important here is that the event summer2020 doesn’t exist in the table. We noticed that only queries that are selecting event_id that doesn’t exist sometimes use a sequential scan.

Table live_event_segmentation increases constantly with about 1 row per second, which is very slow. Autovacuum is working and the table is correctly analyzed. Also this happens constantly, we constantly have sequential scans on this table because of the query above, not int some strange spikes.

prepare p1(text, int) AS select 1 as "one" from "public"."live_event_segmentation" where "public"."live_event_segmentation"."event_id" = $1 limit $2;