Weird Postgres index

gistfile1.txt

EXPLAIN ANALYZE
SELECT "Game"."id" FROM "Game"
WHERE ("Game"."id")
IN (SELECT "t0"."id" FROM "Game" AS "t0" INNER JOIN "Player" AS "j0" ON ("j0"."id") = ("t0"."playerId") WHERE ("j0"."id" = 'gargoyl'))
ORDER BY "Game"."startAt" ASC
LIMIT 1



                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=173320.31..173320.43 rows=1 width=73) (actual time=14471.739..14481.252 rows=1 loops=1)
   ->  Gather Merge  (cost=173320.31..176420.83 rows=26574 width=73) (actual time=14463.110..14472.564 rows=1 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=172320.29..172353.51 rows=13287 width=73) (actual time=14437.835..14437.968 rows=1 loops=3)
               Sort Key: "Game"."startAt"
               Sort Method: top-N heapsort  Memory: 25kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Hash Semi Join  (cost=66314.92..172253.86 rows=13287 width=73) (actual time=936.393..14353.436 rows=10876 loops=3)
                     Hash Cond: ("Game".id = t0.id)
                     ->  Parallel Seq Scan on "Game"  (cost=0.00..102916.59 rows=1095059 width=73) (actual time=0.025..6642.974 rows=876047 loops=3)
                     ->  Hash  (cost=65916.32..65916.32 rows=31888 width=65) (actual time=870.047..870.136 rows=32629 loops=3)
                           Buckets: 32768  Batches: 1  Memory Usage: 3347kB
                           ->  Nested Loop  (cost=807.85..65916.32 rows=31888 width=65) (actual time=9.042..652.461 rows=32629 loops=3)
                                 ->  Index Only Scan using "Player_pkey" on "Player" j0  (cost=0.29..4.31 rows=1 width=8) (actual time=0.059..0.072 rows=1 loops=3)
                                       Index Cond: (id = 'gargoyl'::text)
                                       Heap Fetches: 0
                                 ->  Bitmap Heap Scan on "Game" t0  (cost=807.56..65593.13 rows=31888 width=73) (actual time=3.248..226.691 rows=32629 loops=3)
                                       Recheck Cond: ("playerId" = 'gargoyl'::text)
                                       Heap Blocks: exact=4845
                                       ->  Bitmap Index Scan on "Game.playerId_startAt_index"  (cost=0.00..799.59 rows=31888 width=0) (actual time=2.712..2.718 rows=32629 loops=3)
                                             Index Cond: ("playerId" = 'gargoyl'::text)
 Planning Time: 0.518 ms
 JIT:
   Functions: 40
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 4.180 ms, Inlining 0.000 ms, Optimization 1.401 ms, Emission 23.753 ms, Total 29.334 ms
 Execution Time: 14484.178 ms
 
 
 
 

EXPLAIN ANALYZE
SELECT "Game"."id" FROM "Game"
WHERE ("Game"."id")
IN (SELECT "t0"."id" FROM "Game" AS "t0" INNER JOIN "Player" AS "j0" ON ("j0"."id") = ("t0"."playerId") WHERE ("j0"."name" = 'gargoyl'))
ORDER BY "Game"."startAt" ASC
LIMIT 1

                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1494.36..1494.36 rows=1 width=73) (actual time=0.117..0.202 rows=0 loops=1)
   ->  Sort  (cost=1494.36..1494.55 rows=78 width=73) (actual time=0.099..0.169 rows=0 loops=1)
         Sort Key: "Game"."startAt"
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=1434.53..1493.97 rows=78 width=73) (actual time=0.080..0.134 rows=0 loops=1)
               ->  HashAggregate  (cost=1433.97..1434.75 rows=78 width=65) (actual time=0.062..0.101 rows=0 loops=1)
                     Group Key: t0.id
                     Batches: 1  Memory Usage: 24kB
                     ->  Nested Loop  (cost=11.58..1433.78 rows=78 width=65) (actual time=0.042..0.065 rows=0 loops=1)
                           ->  Index Scan using "Player.name_unique" on "Player" j0  (cost=0.29..8.31 rows=1 width=8) (actual time=0.025..0.032 rows=0 loops=1)
                                 Index Cond: (name = 'gargoyl'::text)
                           ->  Bitmap Heap Scan on "Game" t0  (cost=11.29..1421.78 rows=369 width=73) (never executed)
                                 Recheck Cond: ("playerId" = j0.id)
                                 ->  Bitmap Index Scan on "Game.playerId_startAt_index"  (cost=0.00..11.20 rows=369 width=0) (never executed)
                                       Index Cond: ("playerId" = j0.id)
               ->  Index Scan using "Game_pkey" on "Game"  (cost=0.56..0.76 rows=1 width=73) (never executed)
                     Index Cond: (id = t0.id)
 Planning Time: 0.763 ms
 Execution Time: 0.272 ms