postgresql – Postgres Planner not using GIN index Occassionally

Platform: Heroku; PostgreSQL Version 13

I have a table around 46GB and there are two GIN indexes on name and email columns
I have a search query that is suppose to search in name and email columns using the indexes defined and most of the time it works fine as explain analyze reveales but some time it just stops using those index and use a totally different index that is not even related to query.
Here is the query

EXPLAIN (analyze true, buffers true, timing true) 
SELECT
    "mytbl".*
FROM
    "mytbl"
WHERE
    "mytbl"."deleted_at" IS NULL
    AND(email ILIKE '%somestr%' OR name ILIKE '%somestr')
ORDER BY
    created_at DESC
LIMIT 10 OFFSET 0;

Explain Analyze info

Limit  (cost=0.09..7439.89 rows=10 width=1439) (actual time=11.523..70482.996 rows=1 loops=1)
  Buffers: shared hit=9533464 read=2941613 dirtied=8
  I/O Timings: read=12722.549
  ->  Index Scan Backward using index_mytbl_on_created_at on mytbl  (cost=0.09..2425376.11 rows=3260 width=1439) (actual time=11.521..70482.992 rows=1 loops=1)
"        Filter: ((deleted_at IS NULL) AND (((email)::text ~~* '%somestr%'::text) OR ((name)::text ~~* '%somestr%'::text)))"
        Rows Removed by Filter: 16165920
        Buffers: shared hit=9533464 read=2941613 dirtied=8
        I/O Timings: read=12722.549
Planning:
  Buffers: shared hit=2
Planning Time: 0.617 ms
Execution Time: 70483.100 ms

index_mytbl_on_created_at is an index on created_at and I am really not sure why the planner is choosing this index.

While it should be like this

Limit  (cost=8102.63..8102.64 rows=10 width=1431) (actual time=140.877..140.881 rows=0 loops=1)
  ->  Sort  (cost=8102.63..8104.18 rows=3095 width=1431) (actual time=140.875..140.878 rows=0 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Bitmap Heap Scan on mytbl  (cost=2026.96..8089.26 rows=3095 width=1431) (actual time=140.866..140.868 rows=0 loops=1)
"              Recheck Cond: (((email)::text ~~* '%somestr%'::text) OR ((name)::text ~~* '%somestr%'::text))"
              Filter: (deleted_at IS NULL)
              ->  BitmapOr  (cost=2026.96..2026.96 rows=3098 width=0) (actual time=140.861..140.862 rows=0 loops=1)
                    ->  Bitmap Index Scan on trgm_idx_mytbl_email  (cost=0.00..928.33 rows=1554 width=0) (actual time=72.876..72.876 rows=0 loops=1)
"                          Index Cond: ((email)::text ~~* '%somestr%'::text)"
                    ->  Bitmap Index Scan on trgm_idx_mytbl_name  (cost=0.00..1098.32 rows=1544 width=0) (actual time=67.981..67.981 rows=0 loops=1)
"                          Index Cond: ((name)::text ~~* '%somestr%'::text)"
Planning Time: 1.078 ms
Execution Time: 141.024 ms

Few things that I have tried.

  1. If I use one column in the query every things works fine and planner choose the right index.
EXPLAIN ANALYSE SELECT
    "mytbl".*
FROM
    "mytbl"
WHERE
    "mytbl"."deleted_at" IS NULL
    and email ILIKE '%somestr%'
ORDER BY
    created_at DESC
LIMIT 10 OFFSET 0; 

explain analyze

Limit  (cost=4067.45..4067.45 rows=10 width=1439) (actual time=801.053..801.055 rows=1 loops=1)
  ->  Sort  (cost=4067.45..4068.27 rows=1636 width=1439) (actual time=801.052..801.053 rows=1 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 26kB
        ->  Bitmap Heap Scan on mytbl (cost=834.54..4060.38 rows=1636 width=1439) (actual time=801.039..801.041 rows=1 loops=1)
"              Recheck Cond: ((email)::text ~~* '%somestr%'::text)"
              Filter: (deleted_at IS NULL)
              Heap Blocks: exact=1
              ->  Bitmap Index Scan on trgm_idx_mytbl_email  (cost=0.00..834.46 rows=1637 width=0) (actual time=801.015..801.015 rows=1 loops=1)
"                    Index Cond: ((email)::text ~~* '%somestr%'::text)"
Planning Time: 0.497 ms
Execution Time: 801.158 ms

working perfectly fine and if I change it with name then it is working as well.

  1. If I remove the
ORDER BY
    created_at DESC

part and run the above query then planner again chooses the right indexes.

Limit  (cost=1371.22..1390.80 rows=10 width=1439) (actual time=789.841..789.845 rows=1 loops=1)
  Buffers: shared hit=47417 read=1942
  I/O Timings: read=8.258
  ->  Bitmap Heap Scan on mytbl (cost=1371.22..7752.83 rows=3260 width=1439) (actual time=789.840..789.843 rows=1 loops=1)
"        Recheck Cond: (((email)::text ~~* '%somestr%'::text) OR ((name)::text ~~* '%somestr%'::text))"
        Filter: (deleted_at IS NULL)
        Heap Blocks: exact=1
        Buffers: shared hit=47417 read=1942
        I/O Timings: read=8.258
        ->  BitmapOr  (cost=1371.22..1371.22 rows=3263 width=0) (actual time=789.809..789.810 rows=0 loops=1)
              Buffers: shared hit=47416 read=1942
              I/O Timings: read=8.258
              ->  Bitmap Index Scan on trgm_idx_mytbl_email  (cost=0.00..834.46 rows=1637 width=0) (actual time=704.337..704.337 rows=1 loops=1)
"                    Index Cond: ((email)::text ~~* '%somestr%'::text)"
                    Buffers: shared hit=45677
              ->  Bitmap Index Scan on trgm_idx_mytbl_name  (cost=0.00..536.44 rows=1626 width=0) (actual time=85.469..85.469 rows=0 loops=1)
"                    Index Cond: ((name)::text ~~* '%somestr%'::text)"
                    Buffers: shared hit=1739 read=1942
                    I/O Timings: read=8.258
Planning:
  Buffers: shared hit=2
Planning Time: 0.587 ms
Execution Time: 789.925 ms

  1. I have manually ran the analyze on the table in question and auto vacuum just completed few hours ago.