Query choosing Bad Index Path (ASC/DESC ordering).

[Valli Annamalai <aishwaryaanns@xxxxxxxxx>]

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))
         Filter: (((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR ((distdbentityauditlog1.module)::text = 'Contacts'::text)) AND ((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR (distdbentityauditlog1.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=552685 read=1464159
 Planning Time: 0.567 ms
 Execution Time: 7583.558 ms
(11 rows)

Doubt:
   In Index Scan Backward using distdbentityauditlog1_46625_temp_mahi3_pkey, the startup time was more. So thinking about whether backward scanning takes more time, created a new index and tested with the same query as follows.

create index distdbentityauditlog1_idx7_46625_temp_mahi3 on distdbentityauditlog1_46625_temp_mahi3(zgid, auditedtime desc, module desc);
analyse public.distdbentityauditlog1_46625_temp_mahi3;

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..393.34 rows=10 width=399) (actual time=8115.775..8116.441 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=519970 read=1496874 written=44
   ->  Index Scan Backward using distdbentityauditlog1_46625_temp_mahi3_pkey on public.distdbentityauditlog1_46625_temp_mahi3 distdbentityauditlog1  (cost=0.43..436209.86 rows=11102 width=399) (actual time=8115.772..8116.435 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))
         Filter: (((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR ((distdbentityauditlog1.module)::text = 'Contacts'::text)) AND ((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR (distdbentityauditlog1.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=519970 read=1496874 written=44
 Planning Time: 1.152 ms
 Execution Time: 8116.518 ms

Still no improvement in performance.
     
If DESC has been removed from ORDER BY clause in query, then the performance is good as follows

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, 2 LIMIT '10'::bigint;

Limit  (cost=0.43..393.34 rows=10 width=399) (actual time=0.471..0.865 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=24 read=111
   ->  Index Scan using distdbentityauditlog1_46625_temp_mahi3_pkey on public.distdbentityauditlog1_46625_temp_mahi3 distdbentityauditlog1  (cost=0.43..436209.86 rows=11102 width=399) (actual time=0.468..0.860 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))
         Filter: (((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR ((distdbentityauditlog1.module)::text = 'Contacts'::text)) AND ((distdbentityauditlog1.recordid = '15842006928391817'::bigint) OR (distdbentityauditlog1.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: 174
         Buffers: shared hit=24 read=111
 Planning Time: 0.442 ms
 Execution Time: 0.923 ms

 Thus how to improve performance for DESC operation here?