0055354: Possible seq. scan in Transaction window types

Description

When a window is declared as Transaction in the Application Dictionary, there is an implicit transactional filter (only draft & modified documents in the last 1 day(s)) that adds the following where clause when opening the window:

(table.Updated>? or table.Processed='N')

If the table doesn't have two indexes, one for the updated and one for the processed column, a seq. scan will be performance.

Note that windows declared as Transaction are usually linked to High Volume tables, so the problem is even bigger.

If there are no other restrictive filter in the where clause, the seq. scan will take place for sure.

See for example the where clause when opening the Sales Invoice window:

from C_Invoice invoice0_ where invoice0_.IsSOTrx='Y' and invoice0_.DocStatus<>'TEMP' and (invoice0_.Updated>? or invoice0_.Processed='N') and (invoice0_.AD_Org_ID in (? , ? , ? , ? , ? , ? , ? , ?)) and (invoice0_.AD_Client_ID in (? , ?)) order by invoice0_.DocumentNo, invoice0_.C_Invoice_ID limit ?

and here for the Sales Order window:

where order0_.C_DocTypeTarget_ID=documentty1_.C_DocType_ID and order0_.IsSOTrx='Y' and documentty1_.IsReturn='N' and (documentty1_.DocSubTypeSO not like 'OB') and (order0_.Updated>? or order0_.Processed='N') and (order0_.AD_Org_ID in (? , ? , ? , ? , ? , ? , ? , ?)) and (order0_.AD_Client_ID in (? , ?)) order by order0_.DocumentNo, order0_.C_Order_ID limit ?

In both cases updated column is indexed, but not processed, so the seq. scan is performed

Steps To Reproduce

Execute the following simplified query, similar to the executed by the system:

select * from c_order
where processed='N' or updated > to_date('28/04/2024')
order by DocumentNo, C_Order_ID limit 100

Check a seq. scan is performed.

Now add a partial index over processed:
CREATE INDEX IF NOT EXISTS c_order_processed
    ON public.c_order USING btree
    (processed COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default
    WHERE processed='N';

Run again the query and check both indexes are used, and the total cost is lower.

Proposed Solution

Check tables related to Transaction windows have both the index over the UPDATED column and the partial index over PROCESSED. Add them where missed.

IMHO this implicit filter should be revisited to filter only by UPDATED to perform even faster (and forgetting about the draft status filter), but this is something to check with PM team.

Issue History
Date Modified	Username	Field	Change
2024-04-29 12:30	vmromanos	New Issue
2024-04-29 12:30	vmromanos	Assigned To	=> Triage Platform Base
2024-04-29 12:30	vmromanos	Modules	=> Core
2024-04-29 12:30	vmromanos	Triggers an Emergency Pack	=> No
2024-04-29 12:36	vmromanos	Description Updated	View Revisions
2024-04-29 12:36	vmromanos	Proposed Solution updated
2024-04-29 12:38	vmromanos	Proposed Solution updated

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.