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
Date: Mon, 7 Feb 2022 14:18:40 +0800
Message-ID: <20220207061840.7emplrlgoeehadn5@jrouhaud> (raw)
In-Reply-To: <CADkhgiLxN_kyWur8B9h0tuFEgSuJD4BgMTLBOQihdn-MBU7ptQ@mail.gmail.com>
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>
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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rjuju123@gmail.com, aishwaryaanns@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Query choosing Bad Index Path
In-Reply-To: <20220207061840.7emplrlgoeehadn5@jrouhaud>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox