Return to Question

Edit:

Our Solution

Based on feedback, we disabled parallel queries by setting max_parallel_workers_per_gather = 0 and the problem went away.

We've also increased statistics targets (even though folks have expressed doubt that will help) and will experiment with ways to enable parallel queries in the future without triggering this same "bad" behavior.

Bonus: latency graph for query before and after disabling parallel queries:

Changing the ORDER BY value (including removing it entirely from the query) had no impact on the query plan.

-- on pg 11 instance with enable_seqscan = OFF

 Sort  (cost=5901559.44..5901570.85 rows=4566 width=36)
   Sort Key: f.date_created
   ->  Nested Loop Left Join  (cost=745.83..5901281.90 rows=4566 width=36)
         ->  Index Scan using flight_pkey on flight f  (cost=0.56..428.99 rows=50 width=24)
               Index Cond: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[]))
         ->  Bitmap Heap Scan on passengers p  (cost=745.27..117695.86 rows=32120 width=28)
               Recheck Cond: (f.uuid = flight_id)
               ->  Bitmap Index Scan on passengers_a08cee2d  (cost=0.00..737.24 rows=32120 width=0)
                     Index Cond: (f.uuid = flight_id)
(9 rows)

I experimented by setting max_parallel_workers_per_gather = 0, which also had the desired effect of coercing the postgres 11 to return a query plan that avoided the sequential scan, but I do not think it is wise to disable that functionality for the database.

-- on pg 11 instance with enable_seqscan = OFF OR max_parallel_workers_per_gather = 0

 Sort  (cost=5901559.44..5901570.85 rows=4566 width=36)
   Sort Key: f.date_created
   ->  Nested Loop Left Join  (cost=745.83..5901281.90 rows=4566 width=36)
         ->  Index Scan using flight_pkey on flight f  (cost=0.56..428.99 rows=50 width=24)
               Index Cond: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[]))
         ->  Bitmap Heap Scan on passengers p  (cost=745.27..117695.86 rows=32120 width=28)
               Recheck Cond: (f.uuid = flight_id)
               ->  Bitmap Index Scan on passengers_a08cee2d  (cost=0.00..737.24 rows=32120 width=0)
                     Index Cond: (f.uuid = flight_id)
(9 rows)