Re: Query choosing Bad Index Path (ASC/DESC ordering).
It seems an issue of data distribution. More likely when traversing without
orderby [default ascending order] matching rows were found quickly. You can
validate the same by using order by 14 asc, 2 asc limit 10.
You can try creating an index on auditlogid desc, auditedtime desc. OR any
other combination with auditedtime which here higher chances of finding
matching rows quickly.
On Tue, Feb 8, 2022 at 11:25 AM Valli Annamalai
wrote:
> Postgres version: 11.4
>
> Problem:
> Query choosing Bad Index Path (ASC/DESC ordering). Details are provided
> below
>
> Table:
> \d public.distdbentityauditlog1_46625_temp_mahi3;
> Table "public.distdbentityauditlog1_46625_temp_mahi3"
> 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_mahi3_pkey" PRIMARY KEY, btree
> (zgid, auditedtime, auditlogid)
> "distdbentityauditlog1_idx1_46625_temp_mahi3" btree (recordid)
> "distdbentityauditlog1_idx2_46625_temp_mahi3" btree (auditlogid)
> "distdbentityauditlog1_idx3_46625_temp_mahi3" btree (relatedid)
> "distdbentityauditlog1_idx4_46625_temp_mahi3" gist (actioninfo
> gist_trgm_ops)
> "distdbentityauditlog1_idx5_46625_temp_mahi3" btree (actioninfo)
> "distdbentityauditlog1_idx6_46625_temp_mahi3" btree (auditedtime DESC,
> module)
>
>
> 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_mahi3
> 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..415.30 rows=10 width=400) (actual
> time=7582.965..7583.477 rows=10 loops=1)
>Output: zgid, auditlogid, recordid, recordname, module, actioninfo,
> relatedid, relatedname, relatedmodule, accountid, accountname, doneby,
> userid, auditedtime, fieldhistoryinfo, isauditlogdata, otherdetails,
> audittype, requesteruserid, actiontype, source
>Buffers: shared hit=552685 read=1464159
>-> Index Scan Backward using
> distdbentityauditlog1_46625_temp_mahi3_pkey on
> public.distdbentityauditlog1_46625_temp_mahi3 distdbentityauditlog1
> (cost=0.43..436281.55 rows=10516 width=400) (actual
> time=7582.962..7583.470 rows=10
> loops=1)
> Output: zgid, auditlogid, recordid, recordname, module,
> actioninfo, relatedid, relatedname, relatedmodule, accountid, accountname,
> doneby, userid, auditedtime, fieldhistoryinfo, isauditlogdata,
> otherdetails, audittype, requesteruserid, actiontype, source
> Index Cond: ((distdbentityauditlog1.zgid = 100) AND
> (distdbentityauditlog1.auditedtime >= '2021-03-27 09:43:17'::timestamp
> without time zone))
>
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;
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,5
