Date: Mon, 7 Feb 2022 14:18:40 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Valli Annamalai <aishwaryaanns@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Query choosing Bad Index Path
Message-ID: <20220207061840.7emplrlgoeehadn5@jrouhaud>
References: <17ec4a8632e.129f0591f14187.1873039138136685603@zohocorp.com>
 <17ec4f92317.114ea3ebf16204.6527580001465167381@zohocorp.com>
 <17ed298eb59.b775c0e124435.7693195395182731900@zohocorp.com>
 <CADkhgiJ+gT_FDKZWgP8oZsy6iRbYMYkmRjsPhqhcT1A2KBgcHA@mail.gmail.com>
 <CADkhgiLxN_kyWur8B9h0tuFEgSuJD4BgMTLBOQihdn-MBU7ptQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CADkhgiLxN_kyWur8B9h0tuFEgSuJD4BgMTLBOQihdn-MBU7ptQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20220207061840.7emplrlgoeehadn5%40jrouhaud>
Precedence: bulk

Hi,
On Mon, Feb 07, 2022 at 11:36:17AM +0530, Valli Annamalai wrote:
> *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):

You already sent the same email less than an hour ago on pgsql-performance
([1]), which is the correct mailing list, please don't post on this mailing
list also.

Note also that sending information as images can be problematic as some people
here (including me) won't be able to see them.  I tried on a web-based MUA and
that's not really better though, as the images are hardly readable and
definitely not grep-able.  You will likely have more answers sending
information in plain text.

[1] https://www.postgresql.org/message-id/CADkhgiJ+gT_FDKZWgP8oZsy6iRbYMYkmRjsPhqhcT1A2KBgcHA@mail.gmail.com