Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0055354Openbravo ERPI. Performancepublic2024-04-29 12:302024-04-29 12:38
Reportervmromanos 
Assigned ToTriage Platform Base 
PrioritynormalSeveritymajorReproducibilitysometimes
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0055354: Possible seq. scan in Transaction window types
DescriptionWhen 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 ReproduceExecute 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 SolutionCheck 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.
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2024-04-29 12:30vmromanosNew Issue
2024-04-29 12:30vmromanosAssigned To => Triage Platform Base
2024-04-29 12:30vmromanosModules => Core
2024-04-29 12:30vmromanosTriggers an Emergency Pack => No
2024-04-29 12:36vmromanosDescription Updatedbug_revision_view_page.php?rev_id=27903#r27903
2024-04-29 12:36vmromanosProposed Solution updated
2024-04-29 12:38vmromanosProposed Solution updated

There are no notes attached to this issue.