Why did the number of live rows fetched by index scans drop after table ANALYZE?

Question

We use PostgreSQL 12 and have a simple table, event_participant, storing 100 GBs of data. event_participant has all the necessary indexes, so all rows are fetched using them, i.e., no rows are fetched using sequential scans.

Usually, it fetches 65 rows/second, but one day at 10 AM, we ran a planned campaign where the number of fetched rows using index scans jumped to 5.4 M rows/second. However, the number of index scans stayed the same at 200 per second. Table content started changing slowly but not enough to trigger autoanalyze because autovacuum_analyze_scale_factor is 0.01 or 1% of the table size.

Worth mentioning is that we configured plan_cache_mode TO force_custom_plan on this database because our app uses Prepared Statements, and we want to avoid generic plans because of live campaigns.

After 3 hours of huge CPU load and index scans, we manually performed an ANALYZE of the event_participant, and the number of live rows fetched by index scans immediately dropped from 5.4 M rows/sec to 450 rows/sec.

I'm trying to figure out how the ANALYZE command impacted the number of live rows fetched by index scans, while the number of index scans stayed the same.

Update - including more details about the table structure and indexes.

> \d+ event_participant
                            Table "public.event_participant"
  Column  |       Type       | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+------------------+-----------+----------+---------+----------+--------------+-------------
 event_id | text             |           | not null |         | extended |              | 
 user_id  | bigint           |           | not null |         | plain    |              | 
 progress | text             |           | not null |         | extended |              | 
 level    | integer          |           | not null | 0       | plain    |              | 
 quality  | double precision |           |          |         | plain    |              | 
Indexes:
    "event_participant_pkey" PRIMARY KEY, btree (user_id, event_id)
    "event_participant_event_id_idx" btree (event_id)
Access method: heap

So, at 10 AM, the campaign with a new event started (new event_id), and the event_participant table started growing. At every user login, the backend app, knowing which events are active, selects all entries by user_id and event_id: SELECT * from event_participant WHERE user_id=? AND event_id=?; to pick up the user's progress.

Answer 1

Once again, since the event started, the event_participant table started growing but not enough to trigger autovacuum_analyze, which would update the query plan.

Before the event started at 10 AM, the event with event_id=tour2023 didn't exist in the table, so during the latest autovacuum_analyze, which happened hours before, the query plan was not aware of tour2023, so it suggested using index event_participant_event_id_idx. I tested the hypothesis by running an EXPLAIN SELECT with non-existing event_id; it uses the index created on it and then filters rows by user_id:

explain select * from event_participant where user_id = 1 and event_id = 'bla';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using event_participant_event_id_idx on event_participant  (cost=0.56..1.61 rows=1 width=1409)
   Index Cond: (event_id = 'bla'::text)
   Filter: (user_id = 1)

which means that after event tour2023 started while running query SELECT * from event_participant WHERE user_id=? AND event_id=?; PostgreSQL used event_participant_event_id_idx to fetch all rows with event_id=tour2023 and then filter desired row by user_id instead of using the composite index "event_participant_pkey" PRIMARY KEY, btree (user_id, event_id). This led to the increased number of lines fetched by index scans, as well as huge CPU usage.

After running ANALYZE manually, the query plan was updated, and the database decided to use a composite index. Hence, index scans' number of fetched rows dropped to 450 rows/sec.

EXPLAIN output when using existing event_id:

explain select * from event_participant where user_id = 1 and event_id = 'tour2023';
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using event_participant_pkey on event_participant  (cost=0.56..2.58 rows=1 width=1409)
   Index Cond: ((user_id = 1) AND (event_id = 'tour2023'::text))

So, the answer is that the query plan was stale, and PostgreSQL decided to use a sub-optimal index.

I'm still missing part of why PostgreSQL used the (event_id) index only, as I expected the query planner to favor the composite index when both user_id and event_id are specified in the query.