From: | Valli Annamalai <aishwaryaanns(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Query choosing Bad Index Path |
Date: | 2022-02-07 06:06:17 |
Message-ID: | CADkhgiLxN_kyWur8B9h0tuFEgSuJD4BgMTLBOQihdn-MBU7ptQ@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):
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2022-02-07 06:14:16 | Re: 2022-01 Commitfest |
Previous Message | Böszörményi Zoltán | 2022-02-07 05:59:51 | [PATCH] Add UPDATE WHERE OFFSET IN clause |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2022-02-07 06:18:14 | Re: Query choosing Bad Index Path |
Previous Message | Valli Annamalai | 2022-02-07 05:15:12 | Query choosing Bad Index Path |