Thread: Query chooses Bad Index Path
Postgres version: 11.4
Problem:
Query choosing Bad Index Path. Details are provided below
Table :
\d public.distdbentityauditlog1_46625_temp_mahi1;
Table "public.distdbentityauditlog1_46625_temp_mahi1"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------
zgid | bigint | | not null |
auditlogid | bigint | | not null |
recordid | bigint | | |
recordname | text | | |
module | character varying(50) | | not null |
actioninfo | character varying(255) | | not null |
relatedid | bigint | | |
relatedname | character varying(255) | | |
relatedmodule | character varying(50) | | |
accountid | bigint | | |
accountname | character varying(255) | | |
doneby | character varying(255) | | not null |
userid | bigint | | |
auditedtime | timestamp without time zone | | not null |
fieldhistoryinfo | text | | |
isauditlogdata | boolean | | not null |
otherdetails | text | | |
audittype | integer | | not null |
requesteruserid | bigint | | |
actiontype | integer | | not null |
source | integer | | not null |
module_lower | character varying(50) | | not null |
Indexes:
"distdbentityauditlog1_46625_temp_mahi1_pkey" PRIMARY KEY, btree (zgid, auditedtime, auditlogid)
"distdbentityauditlog1_46625_temp_mahi1_actioninfo_idx" gist (actioninfo gist_trgm_ops)
"distdbentityauditlog1_46625_temp_mahi1_actioninfo_idx1" btree (actioninfo)
"distdbentityauditlog1_46625_temp_mahi1_auditedtime_module_idx" btree (auditedtime DESC, module)
"distdbentityauditlog1_46625_temp_mahi1_auditlogid_idx" btree (auditlogid DESC)
"distdbentityauditlog1_46625_temp_mahi1_idx5" btree (module)
"distdbentityauditlog1_46625_temp_mahi1_idx6" btree (recordid, auditedtime DESC)
"distdbentityauditlog1_46625_temp_mahi1_idx7" btree (relatedid, auditedtime DESC)
explain (analyse, buffers, verbose) SELECT zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requesteruserid, actiontype, source FROM public.distdbentityauditlog1_46625_temp_mahi1 distdbentityauditlog1 WHERE ((actiontype = ANY ('{2,9,14,55,56,67}'::integer[])) AND ((recordid = '15842006928391817'::bigint) OR ((module)::text = 'Contacts'::text)) AND ((recordid = '15842006928391817'::bigint) OR (relatedid = '15842006928391817'::bigint)) AND (audittype <> ALL ('{2,4,5,6}'::integer[])) AND (auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone) AND (zgid = 100)) ORDER BY 14 DESC, 2 DESC LIMIT '10'::bigint;
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):
alter table public.distdbentityauditlog1_46625_temp_mahi1 drop constraint distdbentityauditlog1_46625_temp_mahi1_pkey;
analyse public.distdbentityauditlog1_46625_temp_mahi1;
explain (analyse, buffers, verbose) SELECT zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requesteruserid, actiontype, source FROM public.distdbentityauditlog1_46625_temp_mahi1 distdbentityauditlog1 WHERE ((actiontype = ANY ('{2,9,14,55,56,67}'::integer[])) AND ((recordid = '15842006928391817'::bigint) OR ((module)::text = 'Contacts'::text)) AND ((recordid = '15842006928391817'::bigint) OR (relatedid = '15842006928391817'::bigint)) AND (audittype <> ALL ('{2,4,5,6}'::integer[])) AND (auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone) AND (zgid = 100)) ORDER BY 14 DESC, 2 DESC LIMIT '10'::bigint;
Problem:
Query choosing Bad Index Path. Details are provided below
Table :
\d public.distdbentityauditlog1_46625_temp_mahi1;
Table "public.distdbentityauditlog1_46625_temp_mahi1"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------
zgid | bigint | | not null |
auditlogid | bigint | | not null |
recordid | bigint | | |
recordname | text | | |
module | character varying(50) | | not null |
actioninfo | character varying(255) | | not null |
relatedid | bigint | | |
relatedname | character varying(255) | | |
relatedmodule | character varying(50) | | |
accountid | bigint | | |
accountname | character varying(255) | | |
doneby | character varying(255) | | not null |
userid | bigint | | |
auditedtime | timestamp without time zone | | not null |
fieldhistoryinfo | text | | |
isauditlogdata | boolean | | not null |
otherdetails | text | | |
audittype | integer | | not null |
requesteruserid | bigint | | |
actiontype | integer | | not null |
source | integer | | not null |
module_lower | character varying(50) | | not null |
Indexes:
"distdbentityauditlog1_46625_temp_mahi1_pkey" PRIMARY KEY, btree (zgid, auditedtime, auditlogid)
"distdbentityauditlog1_46625_temp_mahi1_actioninfo_idx" gist (actioninfo gist_trgm_ops)
"distdbentityauditlog1_46625_temp_mahi1_actioninfo_idx1" btree (actioninfo)
"distdbentityauditlog1_46625_temp_mahi1_auditedtime_module_idx" btree (auditedtime DESC, module)
"distdbentityauditlog1_46625_temp_mahi1_auditlogid_idx" btree (auditlogid DESC)
"distdbentityauditlog1_46625_temp_mahi1_idx5" btree (module)
"distdbentityauditlog1_46625_temp_mahi1_idx6" btree (recordid, auditedtime DESC)
"distdbentityauditlog1_46625_temp_mahi1_idx7" btree (relatedid, auditedtime DESC)
explain (analyse, buffers, verbose) SELECT zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requesteruserid, actiontype, source FROM public.distdbentityauditlog1_46625_temp_mahi1 distdbentityauditlog1 WHERE ((actiontype = ANY ('{2,9,14,55,56,67}'::integer[])) AND ((recordid = '15842006928391817'::bigint) OR ((module)::text = 'Contacts'::text)) AND ((recordid = '15842006928391817'::bigint) OR (relatedid = '15842006928391817'::bigint)) AND (audittype <> ALL ('{2,4,5,6}'::integer[])) AND (auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone) AND (zgid = 100)) ORDER BY 14 DESC, 2 DESC LIMIT '10'::bigint;
Limit (cost=0.43..438.62 rows=10 width=400) (actual time=8045.030..8045.576 rows=10 loops=1)
Output: zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requesteru
serid, actiontype, source
Buffers: shared hit=548660 read=1485553
-> Index Scan Backward using distdbentityauditlog1_46625_temp_mahi1_pkey on public.distdbentityauditlog1_46625_temp_mahi1 distdbentityauditlog1 (cost=0.43..445948.91 rows=10177 width=400) (actual time=8045.027..8045.569 rows=10
loops=1)
Output: zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requ
esteruserid, actiontype, source
Index Cond: ((distdbentityauditlog1.zgid = 100) AND (distdbentityauditlog1.auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone))
Filter: (((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR ((distdbentityauditlog1.module)::text = 'Contacts'::text)) AND ((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR (distdbentityaudi
tlog1.relatedid = '15842006928391817'::bigint)) AND (distdbentityauditlog1.audittype <> ALL ('{2,4,5,6}'::integer[])) AND (distdbentityauditlog1.actiontype = ANY ('{2,9,14,55,56,67}'::integer[])))
Rows Removed by Filter: 2943989
Buffers: shared hit=548660 read=1485553
Planning Time: 0.530 ms
Execution Time: 8045.687 ms
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):
alter table public.distdbentityauditlog1_46625_temp_mahi1 drop constraint distdbentityauditlog1_46625_temp_mahi1_pkey;
analyse public.distdbentityauditlog1_46625_temp_mahi1;
explain (analyse, buffers, verbose) SELECT zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requesteruserid, actiontype, source FROM public.distdbentityauditlog1_46625_temp_mahi1 distdbentityauditlog1 WHERE ((actiontype = ANY ('{2,9,14,55,56,67}'::integer[])) AND ((recordid = '15842006928391817'::bigint) OR ((module)::text = 'Contacts'::text)) AND ((recordid = '15842006928391817'::bigint) OR (relatedid = '15842006928391817'::bigint)) AND (audittype <> ALL ('{2,4,5,6}'::integer[])) AND (auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone) AND (zgid = 100)) ORDER BY 14 DESC, 2 DESC LIMIT '10'::bigint;
Limit (cost=140917.99..140918.01 rows=10 width=402) (actual time=103.667..103.673 rows=10 loops=1)
Output: zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requesteru
serid, actiontype, source
Buffers: shared read=10448 written=9
-> Sort (cost=140917.99..140942.38 rows=9759 width=402) (actual time=103.665..103.667 rows=10 loops=1)
Output: zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype, requ
esteruserid, actiontype, source
Sort Key: distdbentityauditlog1.auditedtime DESC, distdbentityauditlog1.auditlogid DESC
Sort Method: top-N heapsort Memory: 34kB
Buffers: shared read=10448 written=9
-> Bitmap Heap Scan on public.distdbentityauditlog1_46625_temp_mahi1 distdbentityauditlog1 (cost=686.74..140707.10 rows=9759 width=402) (actual time=12.291..79.847 rows=16824 loops=1)
Output: zgid, auditlogid, recordid, recordname, module, actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname, doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails, audittype
, requesteruserid, actiontype, source
Recheck Cond: (((distdbentityauditlog1.recordid = '15842006928391817'::bigint) AND (distdbentityauditlog1.auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone)) OR ((distdbentityauditlog1.relatedid = '158
42006928391817'::bigint) AND (distdbentityauditlog1.auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone)))
Filter: ((distdbentityauditlog1.zgid = 100) AND ((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR ((distdbentityauditlog1.module)::text = 'Contacts'::text)) AND (distdbentityauditlog1.audittype <>
ALL ('{2,4,5,6}'::integer[])) AND (distdbentityauditlog1.actiontype = ANY ('{2,9,14,55,56,67}'::integer[])))
Heap Blocks: exact=10267
Buffers: shared read=10448 written=9
-> BitmapOr (cost=686.74..686.74 rows=32499 width=0) (actual time=9.464..9.464 rows=0 loops=1)
Buffers: shared read=181
-> Bitmap Index Scan on distdbentityauditlog1_46625_temp_mahi1_idx6 (cost=0.00..348.93 rows=16250 width=0) (actual time=5.812..5.812 rows=16928 loops=1)
Index Cond: ((distdbentityauditlog1.recordid = '15842006928391817'::bigint) AND (distdbentityauditlog1.auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone))
Buffers: shared read=95
-> Bitmap Index Scan on distdbentityauditlog1_46625_temp_mahi1_idx7 (cost=0.00..332.93 rows=16250 width=0) (actual time=3.650..3.650 rows=16824 loops=1)
Index Cond: ((distdbentityauditlog1.relatedid = '15842006928391817'::bigint) AND (distdbentityauditlog1.auditedtime >= '2021-03-27 09:43:17'::timestamp without time zone))
Buffers: shared read=86
Planning Time: 1.110 ms
Execution Time: 103.755 ms
It's a bit annoying that you post the same query over and over again, starting a new thread every time. Don't do that, please, it's just confusing, people lose track of information you already provided in other threads etc. Now, to the question ... On 2/9/22 06:37, Valli Annamalai wrote: > Postgres version: 11.4 > > Problem: > Query choosing Bad Index Path. Details are provided below > > ... > > > 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. Well, the filter condition is much more complex - it's not just conditions on recordid, but various conditions on other columns, with both AND and OR. So it's possible the estimate is off, and the optimizer picks the wrong plan. Try running explain analyze without the LIMIT, that'll tell you how accurate the estimates are (LIMIT terminates early, so the actual rowcount is incomplete). The other option is data distribution issue, as pointed out by Monika Yadav in the other thread. The optimizer assumes matching rows are distributed uniformly in the input relation, but chances are they're either close to beginning/end depending on how you sort it. Imagine you have 1000000 rows, 1000 of them match the filter, and you have LIMIT 10. It the matching rows are distributed uniformly, it's enough to scan 1% of the input, i.e. 10000 rows (because there's one matching row for every 1000 rows, on average). But let's assume the matching rows are not distributed uniformly, but at the end, when you sort it. Well, you'll have go through 100% of the input. But the optimizer won't realize that. This is a known / common issue with LIMIT, unfortunately. The estimated cost is much lower that it should be, and it's hard to fix. > 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): > > Well, yeah. That's mostly consistent with the data distribution theory. I'd try two things: 1) define a covering index, so that the query can do Index Only Scan 2) define partial index, moving some of the filter conditions to index predicate (not sure if that's possible, it depends on what parameters of the condition are static) regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company