Query choosing Bad Index Path

[Valli Annamalai <aishwaryaanns@xxxxxxxxx>]

Postgres version: 11.4

Problem:

    Query choosing Bad Index Path. Details are provided below:

Table :

Doubt

   1. Why is this Query choosing Index Scan Backward using table1_pkey Index though it's cost is high. It can rather choose
            BITMAP OR

                  (Index on RECORDID) i.e; table1_idx6

                  (Index on RELATEDID) i.e; table1_idx7

      Below is the selectivity details from pg_stats table

        - Recordid has 51969 distinct values. And selectivity (most_common_freqs) for recordid = 15842006928391817 is 0.00376667

        - Relatedid has 82128 distinct values. And selectivity (most_common_freqs) for recordid = 15842006928391817 is 0.0050666

Since, selectivity is less, this should logically choose this Index, which would have improve my query performance here.

I cross-checked the same by removing PrimaryKey to this table and query now chooses these indexes and response is in 100ms. Please refer the plan below (after removing primary key):