Query choosing Bad Index Path

From: Valli Annamalai <aishwaryaanns(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query choosing Bad Index Path
Date: 2022-02-07 05:15:12
Message-ID: CADkhgiJ+gT_FDKZWgP8oZsy6iRbYMYkmRjsPhqhcT1A2KBgcHA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

*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):

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2022-02-07 05:22:08 Move replication slot structures/enums/macros to a new header file for better usability by external tools/modules
Previous Message Fujii Masao 2022-02-07 04:51:31 Re: Add checkpoint and redo LSN to LogCheckpointEnd log message

Browse pgsql-performance by date

  From Date Subject
Next Message Valli Annamalai 2022-02-07 06:06:17 Query choosing Bad Index Path
Previous Message Vijaykumar Jain 2022-02-05 21:25:02 Re: Terribly slow query with very good plan?