Toolbox

Main Page

Upload file

What links here

Recent changes

Help

PDF Books

Show collection (0 pages)

Collections help

Search

Participate

Projects:Foreign Key Filtering Performance Improvement/QA

1 Use Cases
- 1.1 With Standard Grid Configuration
- 1.2 With allowFkFilterByIdentifier Grid Configuration set to false
2 Performance Testing
- 2.1 Filtering by ID Vs Filtering by Identifier
- 2.2 Left Join Vs Inner Join
3 Query Plan Comparison in PostgreSQL 9.1
- 3.1 Filtering by ID Vs Filtering by Identifier
- 3.2 Filtering with 'left join' Vs Filtering with 'inner join'

Use Cases

These use cases have been added to testlink in the folder Performance Improvement of Foreign Key Filters.

As F&B Admin, open the Sales Order window. Filter the grid using the Business Partner column. Keep the debugging tools opened.

With Standard Grid Configuration

1a) Filter by selecting a row in the filter drop down

Open the filter drop down
Select any record
Check that the criteria sent in the datasource request is like:

{ fieldName: businessPartner', operator:'equals', value: '<<businessPartnerId>>'}

The ==<<businessPartnerIdentifier>> text is entered in the filter editor

2a) Filter by entering text manually in the filter editor

Clear the filters
Enter a substring of a business partner in the filter editor
Check that the criteria sent in the datasource request is like:

{ fieldName: businessPartner$_identifier', operator:'iContains', value: '<<businessPartnerSubString>>'}

3a) Filter by selecting a row in the filter drop down, then modifying it manually

Open the filter drop down
Select any record
Check that the criteria sent in the datasource request is like:

{ fieldName: businessPartner', operator:'equals', value: '<<businessPartnerId>>'}

The ==<<businessPartnerIdentifier>> text is entered in the filter editor
Modify the business partner identifier in the filter editor and move the focus to the next column

{ fieldName: businessPartner$_identifier', operator:'iContains', value: '<<businessPartnerSubString>>'}

With allowFkFilterByIdentifier Grid Configuration set to false

1b) Filter by selecting a row in the filter drop down

Open the filter drop down
Select any record
Check that the criteria sent in the datasource request is like:

{ fieldName: businessPartner', operator:'equals', value: '<<businessPartnerId>>'}

The ==<<businessPartnerIdentifier>> text is entered in the filter editor

2b) Filter by entering text manually in the filter editor

Clear the filters
Enter a substring of a business partner in the filter editor
Check that no datasource request is done, and that the filter editor is emptied

3b) Filter by selecting a row in the filter drop down, then modifying it manually

Open the filter drop down
Select any record
Check that the criteria sent in the datasource request is like:

{ fieldName: businessPartner', operator:'equals', value: '<<businessPartnerId>>'}

The ==<<businessPartnerIdentifier>> text is entered in the filter editor
Modify the business partner identifier in the filter editor and move the focus to the next column
Check that no datasource request is done and that the filter editor content has been reverted to its previous state.

Performance Testing

Tested in a database with 226803 orders and 563 business partner.

The time measure has been obtained from the Time (ms) column from the Network tab of the Chrome debugger. Each test has been repeated ten times, the value shown here is the average time per request.

Filtering by ID Vs Filtering by Identifier

Two types of queries are tested:

Filtering by the identifier of the foreign key (using the equals operator)
Filtering by the id of the foreign key (using the equals operator)

The first type of query is the query done when a record was selected in a filter drop down before this project was implemented. The second type of query is the query done when a record was selected in a filter drop down after the project completion. Filtering the Business Partner column of the header tab of the Sales Order window:

Filter by identifier: 796 ms
Filtering by id: 52 ms (93.45% faster)

Filtering the Business Partner Address column of the header tab of the Sales Order window:

Filter by identifier: 791 ms
Filtering by id: 178 ms (77.49% faster)

The improvement is bigger when filtering by the Business Partner columne because there is an index on c_bpartner_id in the c_order table, while there is no index for c_bpartner_location_id in that table.

Left Join Vs Inner Join

Entering in the Business Partner filter editor a substring that results in 27944 hits (only the first 100 are retrieved):

Using Left Join: 753 ms
Using Inner Join: 152 ms (79.81% faster)

Entering in the Business Partner filter editor a substring that results in 343 hits (only the first 100 are retrieved):

Using Left Join: 732 ms
Using Inner Join: 23 ms (96.85% faster)

Query Plan Comparison in PostgreSQL 9.1

Filtering by ID Vs Filtering by Identifier

Full Query (filtering by identifier)

 
SELECT order0_.C_Order_ID AS C1_449_, order0_.AD_Client_ID AS AD2_449_, order0_.AD_Org_ID AS AD3_449_, order0_.IsActive AS IsActive449_, 
order0_.Created AS Created449_, order0_.CreatedBy AS CreatedBy449_, order0_.Updated AS Updated449_, order0_.UpdatedBy AS UpdatedBy449_,
 order0_.IsSOTrx AS IsSOTrx449_, order0_.DocumentNo AS DocumentNo449_, order0_.DocStatus AS DocStatus449_, order0_.DocAction AS DocAction449_, 
order0_.Processing AS Processing449_, order0_.Processed AS Processed449_, order0_.C_DocType_ID AS C15_449_, order0_.C_DocTypeTarget_ID AS C16_449_, 
order0_.Description AS Descrip17_449_, order0_.IsDelivered AS IsDeliv18_449_, order0_.IsInvoiced AS IsInvoiced449_, 
order0_.IsPrinted AS IsPrinted449_, order0_.IsSelected AS IsSelected449_, order0_.SalesRep_ID AS SalesRep22_449_, 
order0_.DateOrdered AS DateOrd23_449_, order0_.DatePromised AS DatePro24_449_, order0_.DatePrinted AS DatePri25_449_, 
order0_.DateAcct AS DateAcct449_, order0_.C_BPartner_ID AS C27_449_, order0_.BillTo_ID AS BillTo28_449_, order0_.C_BPartner_Location_ID AS C29_449_,
 order0_.POReference AS PORefer30_449_, order0_.IsDiscountPrinted AS IsDisco31_449_, order0_.C_Currency_ID AS C32_449_, 
order0_.PaymentRule AS Payment33_449_, order0_.C_PaymentTerm_ID AS C34_449_, order0_.InvoiceRule AS Invoice35_449_, 
order0_.DeliveryRule AS Deliver36_449_, order0_.FreightCostRule AS Freight37_449_, order0_.FreightAmt AS FreightAmt449_, 
order0_.DeliveryViaRule AS Deliver39_449_, order0_.M_Shipper_ID AS M40_449_, order0_.C_Charge_ID AS C41_449_, order0_.ChargeAmt AS ChargeAmt449_, 
order0_.PriorityRule AS Priorit43_449_, order0_.TotalLines AS TotalLines449_, order0_.GrandTotal AS GrandTotal449_, 
order0_.M_Warehouse_ID AS M46_449_, order0_.M_PriceList_ID AS M47_449_, order0_.IsTaxIncluded AS IsTaxIn48_449_, 
order0_.C_Campaign_ID AS C49_449_, order0_.C_Project_ID AS C50_449_, order0_.C_Activity_ID AS C51_449_, order0_.Posted AS Posted449_, 
order0_.AD_User_ID AS AD53_449_, order0_.CopyFrom AS CopyFrom449_, order0_.DropShip_BPartner_ID AS DropShip55_449_, 
order0_.DropShip_Location_ID AS DropShip56_449_, order0_.DropShip_User_ID AS DropShip57_449_, order0_.IsSelfService AS IsSelfS58_449_, 
order0_.AD_OrgTrx_ID AS AD59_449_, order0_.User1_ID AS User60_449_, order0_.User2_ID AS User61_449_, order0_.Deliverynotes AS Deliver62_449_, 
order0_.C_Incoterms_ID AS C63_449_, order0_.Incotermsdescription AS Incoter64_449_, order0_.Generatetemplate AS Generat65_449_, 
order0_.Delivery_Location_ID AS Delivery66_449_, order0_.CopyFromPO AS CopyFromPO449_, order0_.FIN_Paymentmethod_ID AS FIN68_449_, 
order0_.FIN_Payment_Priority_ID AS FIN69_449_, order0_.RM_PickFromShipment AS RM70_449_, order0_.RM_ReceiveMaterials AS RM71_449_, 
order0_.RM_CreateInvoice AS RM72_449_, order0_.C_Return_Reason_ID AS C73_449_, order0_.RM_AddOrphanLine AS RM74_449_, order0_.A_Asset_ID AS A75_449_, 
order0_.Calculate_Promotions AS Calculate76_449_, order0_.C_Costcenter_ID AS C77_449_, order0_.Convertquotation AS Convert78_449_, 
order0_.C_Reject_Reason_ID AS C79_449_, order0_.validuntil AS validuntil449_, order0_.Quotation_ID AS Quotation81_449_, 
order0_.SO_Res_Status AS SO82_449_, order0_.Create_POLines AS Create83_449_, order0_.Iscashvat AS Iscashvat449_, 
order0_.RM_Pickfromreceipt AS RM85_449_, order0_.EM_APRM_AddPayment AS EM86_449_ 
FROM C_Order order0_ 
LEFT JOIN C_BPartner businesspa1_ ON order0_.C_BPartner_ID=businesspa1_.C_BPartner_ID 
CROSS JOIN C_DocType documentty2_ 
WHERE order0_.C_DocTypeTarget_ID=documentty2_.C_DocType_ID 
AND upper(coalesce(to_char(businesspa1_.Name), ''))=upper('Unsold') AND order0_.IsSOTrx='Y' AND documentty2_.IsReturn='N' 
AND (documentty2_.DocSubTypeSO NOT LIKE 'OB') 
AND (order0_.AD_Org_ID IN ('93026108D6314843BDAF1E2C9582029E' , '52096B938D1B4A0BBED92CC51175D8B5' , 'C640F59345554138AA2278DA89B3D525' , '1860BD190DCC4A3BA8441B7D55178E61' , '16510591DECE46039236AD9E96D7FB55' , '994E4D2FA96A4C66B063E0A58E009481' , '4F5E7419E38A437B9381C738F4891E3F' , '0' , '951205728A984C9585E3C2179B4F2418' , 'A598837D7B0446649105844584E6565A' , 'C9040AD8D77642D98F89A5201E1EE1F5' , '190B2FF54907429591CE631C2A03BF24' , 'CFC24A4468734E5D9A05A7ACD63C78F5' , '725320F3DF8A4FD7A35F43B38CF7427C' , 'B9886DDD96124C5B8BE0F7BFADE5E3C3' , '9DE20F09DABA46BFA99DAFA52D9A6624' , '9C753DB213FA4F8B9FADA2136DFEF83E' , '77A6A6E722384AF884E80AC54D0E9A14' , 'A79072D955404CB3BC5F79C455560A62' , '19BB9330D92F4D12AA94DF1D26C1C643' , 'D448757A4607489C8915CDC5469CC1DD')) 
AND (order0_.AD_Client_ID IN ('7273232E589944E3B2C314CCFA24CE51' , '0')) 
ORDER BY order0_.DateOrdered DESC, order0_.C_Order_ID DESC 
LIMIT 100

Explain Plan (filtering by identifier)

Limit  (cost=65092.66..65092.91 rows=100 width=3000)
 ->  Sort  (cost=65092.66..65094.21 rows=618 width=3000)
       Sort Key: order0_.dateordered, order0_.c_order_id
       ->  Hash Left Join  (cost=72.63..65069.04 rows=618 width=3000)
             Hash Cond: ((order0_.c_bpartner_id)::text = (businesspa1_.c_bpartner_id)::text)
             Filter: (upper((COALESCE(to_char(businesspa1_.name), ::character varying))::text) = 'UNSOLD'::text)
             ->  Hash Join  (cost=9.96..31758.78 rows=123712 width=3000)
                   Hash Cond: ((order0_.c_doctypetarget_id)::text = (documentty2_.c_doctype_id)::text)
                   ->  Seq Scan on c_order order0_  (cost=0.00..29954.99 rows=148455 width=3000)
                         Filter: (((ad_client_id)::text = ANY ('{7273232E589944E3B2C314CCFA24CE51,0}'::text[])) AND (issotrx = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{93026108D6314843BDAF1E2C9582029E,52096B938D1B4A0BBED92CC51175D8B5,C640F59345554138A (...)
                   ->  Hash  (cost=8.34..8.34 rows=130 width=32)
                         ->  Seq Scan on c_doctype documentty2_  (cost=0.00..8.34 rows=130 width=32)
                               Filter: (((docsubtypeso)::text !~~ 'OB'::text) AND (isreturn = 'N'::bpchar))
             ->  Hash  (cost=55.63..55.63 rows=563 width=49)
                   ->  Seq Scan on c_bpartner businesspa1_  (cost=0.00..55.63 rows=563 width=49)

Query plan when filtering by identifier

Full Query (filtering by id)

SELECT order0_.C_Order_ID AS C1_449_, order0_.AD_Client_ID AS AD2_449_, order0_.AD_Org_ID AS AD3_449_, order0_.IsActive AS IsActive449_, 
order0_.Created AS Created449_, order0_.CreatedBy AS CreatedBy449_, order0_.Updated AS Updated449_, order0_.UpdatedBy AS UpdatedBy449_,
 order0_.IsSOTrx AS IsSOTrx449_, order0_.DocumentNo AS DocumentNo449_, order0_.DocStatus AS DocStatus449_, order0_.DocAction AS DocAction449_, 
order0_.Processing AS Processing449_, order0_.Processed AS Processed449_, order0_.C_DocType_ID AS C15_449_, order0_.C_DocTypeTarget_ID AS C16_449_, 
order0_.Description AS Descrip17_449_, order0_.IsDelivered AS IsDeliv18_449_, order0_.IsInvoiced AS IsInvoiced449_, 
order0_.IsPrinted AS IsPrinted449_, order0_.IsSelected AS IsSelected449_, order0_.SalesRep_ID AS SalesRep22_449_, 
order0_.DateOrdered AS DateOrd23_449_, order0_.DatePromised AS DatePro24_449_, order0_.DatePrinted AS DatePri25_449_, 
order0_.DateAcct AS DateAcct449_, order0_.C_BPartner_ID AS C27_449_, order0_.BillTo_ID AS BillTo28_449_, order0_.C_BPartner_Location_ID AS C29_449_,
 order0_.POReference AS PORefer30_449_, order0_.IsDiscountPrinted AS IsDisco31_449_, order0_.C_Currency_ID AS C32_449_, 
order0_.PaymentRule AS Payment33_449_, order0_.C_PaymentTerm_ID AS C34_449_, order0_.InvoiceRule AS Invoice35_449_, 
order0_.DeliveryRule AS Deliver36_449_, order0_.FreightCostRule AS Freight37_449_, order0_.FreightAmt AS FreightAmt449_, 
order0_.DeliveryViaRule AS Deliver39_449_, order0_.M_Shipper_ID AS M40_449_, order0_.C_Charge_ID AS C41_449_, order0_.ChargeAmt AS ChargeAmt449_, 
order0_.PriorityRule AS Priorit43_449_, order0_.TotalLines AS TotalLines449_, order0_.GrandTotal AS GrandTotal449_, 
order0_.M_Warehouse_ID AS M46_449_, order0_.M_PriceList_ID AS M47_449_, order0_.IsTaxIncluded AS IsTaxIn48_449_, 
order0_.C_Campaign_ID AS C49_449_, order0_.C_Project_ID AS C50_449_, order0_.C_Activity_ID AS C51_449_, order0_.Posted AS Posted449_, 
order0_.AD_User_ID AS AD53_449_, order0_.CopyFrom AS CopyFrom449_, order0_.DropShip_BPartner_ID AS DropShip55_449_, 
order0_.DropShip_Location_ID AS DropShip56_449_, order0_.DropShip_User_ID AS DropShip57_449_, order0_.IsSelfService AS IsSelfS58_449_, 
order0_.AD_OrgTrx_ID AS AD59_449_, order0_.User1_ID AS User60_449_, order0_.User2_ID AS User61_449_, order0_.Deliverynotes AS Deliver62_449_, 
order0_.C_Incoterms_ID AS C63_449_, order0_.Incotermsdescription AS Incoter64_449_, order0_.Generatetemplate AS Generat65_449_, 
order0_.Delivery_Location_ID AS Delivery66_449_, order0_.CopyFromPO AS CopyFromPO449_, order0_.FIN_Paymentmethod_ID AS FIN68_449_, 
order0_.FIN_Payment_Priority_ID AS FIN69_449_, order0_.RM_PickFromShipment AS RM70_449_, order0_.RM_ReceiveMaterials AS RM71_449_, 
order0_.RM_CreateInvoice AS RM72_449_, order0_.C_Return_Reason_ID AS C73_449_, order0_.RM_AddOrphanLine AS RM74_449_, order0_.A_Asset_ID AS A75_449_, 
order0_.Calculate_Promotions AS Calculate76_449_, order0_.C_Costcenter_ID AS C77_449_, order0_.Convertquotation AS Convert78_449_, 
order0_.C_Reject_Reason_ID AS C79_449_, order0_.validuntil AS validuntil449_, order0_.Quotation_ID AS Quotation81_449_, 
order0_.SO_Res_Status AS SO82_449_, order0_.Create_POLines AS Create83_449_, order0_.Iscashvat AS Iscashvat449_, 
order0_.RM_Pickfromreceipt AS RM85_449_, order0_.EM_APRM_AddPayment AS EM86_449_ FROM C_Order order0_ CROSS JOIN C_DocType documentty1_ 
WHERE order0_.C_DocTypeTarget_ID=documentty1_.C_DocType_ID AND order0_.C_BPartner_ID='1226FB6C20E343A6B51D671C60ACD822' 
AND order0_.IsSOTrx='Y' AND documentty1_.IsReturn='N' AND (documentty1_.DocSubTypeSO NOT LIKE 'OB') 
AND (order0_.AD_Org_ID IN ('93026108D6314843BDAF1E2C9582029E' , '52096B938D1B4A0BBED92CC51175D8B5' , 'C640F59345554138AA2278DA89B3D525' , '1860BD190DCC4A3BA8441B7D55178E61' , '16510591DECE46039236AD9E96D7FB55' , '994E4D2FA96A4C66B063E0A58E009481' , '4F5E7419E38A437B9381C738F4891E3F' , '0' , '951205728A984C9585E3C2179B4F2418' , 'A598837D7B0446649105844584E6565A' , 'C9040AD8D77642D98F89A5201E1EE1F5' , '190B2FF54907429591CE631C2A03BF24' , 'CFC24A4468734E5D9A05A7ACD63C78F5' , '725320F3DF8A4FD7A35F43B38CF7427C' , 'B9886DDD96124C5B8BE0F7BFADE5E3C3' , '9DE20F09DABA46BFA99DAFA52D9A6624' , '9C753DB213FA4F8B9FADA2136DFEF83E' , '77A6A6E722384AF884E80AC54D0E9A14' , 'A79072D955404CB3BC5F79C455560A62' , '19BB9330D92F4D12AA94DF1D26C1C643' , 'D448757A4607489C8915CDC5469CC1DD')) 
AND (order0_.AD_Client_ID IN ('7273232E589944E3B2C314CCFA24CE51' , '0')) 
ORDER BY order0_.DateOrdered DESC, order0_.C_Order_ID DESC 
LIMIT 100

Explan Plan (filtering by id)

Limit  (cost=400.86..400.99 rows=54 width=3000)
 ->  Sort  (cost=400.86..400.99 rows=54 width=3000)
       Sort Key: order0_.dateordered, order0_.c_order_id
       ->  Hash Join  (cost=15.05..399.30 rows=54 width=3000)
             Hash Cond: ((order0_.c_doctypetarget_id)::text = (documentty1_.c_doctype_id)::text)
             ->  Bitmap Heap Scan on c_order order0_  (cost=5.08..388.55 rows=65 width=3000)
                   Recheck Cond: ((c_bpartner_id)::text = '1226FB6C20E343A6B51D671C60ACD822'::text)
                   Filter: (((ad_client_id)::text = ANY ('{7273232E589944E3B2C314CCFA24CE51,0}'::text[])) AND (issotrx = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{93026108D6314843BDAF1E2C9582029E,52096B938D1B4A0BBED92CC51175D8B5,C640F59345554138AA2278D (...)
                   ->  Bitmap Index Scan on c_order_bpartner  (cost=0.00..5.07 rows=100 width=0)
                         Index Cond: ((c_bpartner_id)::text = '1226FB6C20E343A6B51D671C60ACD822'::text)
             ->  Hash  (cost=8.34..8.34 rows=130 width=32)
                   ->  Seq Scan on c_doctype documentty1_  (cost=0.00..8.34 rows=130 width=32)
                         Filter: (((docsubtypeso)::text !~~ 'OB'::text) AND (isreturn = 'N'::bpchar))

Query plan when filtering by ID

Filtering with 'left join' Vs Filtering with 'inner join'

Full query (left join)

SELECT order0_.C_Order_ID AS C1_449_, order0_.AD_Client_ID AS AD2_449_, order0_.AD_Org_ID AS AD3_449_, order0_.IsActive AS IsActive449_, 
order0_.Created AS Created449_, order0_.CreatedBy AS CreatedBy449_, order0_.Updated AS Updated449_, order0_.UpdatedBy AS UpdatedBy449_, 
order0_.IsSOTrx AS IsSOTrx449_, order0_.DocumentNo AS DocumentNo449_, order0_.DocStatus AS DocStatus449_, order0_.DocAction AS DocAction449_, 
order0_.Processing AS Processing449_, order0_.Processed AS Processed449_, order0_.C_DocType_ID AS C15_449_, order0_.C_DocTypeTarget_ID AS C16_449_, 
order0_.Description AS Descrip17_449_, order0_.IsDelivered AS IsDeliv18_449_, order0_.IsInvoiced AS IsInvoiced449_, order0_.IsPrinted AS IsPrinted449_,
 order0_.IsSelected AS IsSelected449_, order0_.SalesRep_ID AS SalesRep22_449_, order0_.DateOrdered AS DateOrd23_449_, 
order0_.DatePromised AS DatePro24_449_, order0_.DatePrinted AS DatePri25_449_, order0_.DateAcct AS DateAcct449_, order0_.C_BPartner_ID AS C27_449_, 
order0_.BillTo_ID AS BillTo28_449_, order0_.C_BPartner_Location_ID AS C29_449_, order0_.POReference AS PORefer30_449_, 
order0_.IsDiscountPrinted AS IsDisco31_449_, order0_.C_Currency_ID AS C32_449_, order0_.PaymentRule AS Payment33_449_, 
order0_.C_PaymentTerm_ID AS C34_449_, order0_.InvoiceRule AS Invoice35_449_, order0_.DeliveryRule AS Deliver36_449_, 
order0_.FreightCostRule AS Freight37_449_, order0_.FreightAmt AS FreightAmt449_, order0_.DeliveryViaRule AS Deliver39_449_, 
order0_.M_Shipper_ID AS M40_449_, order0_.C_Charge_ID AS C41_449_, order0_.ChargeAmt AS ChargeAmt449_, order0_.PriorityRule AS Priorit43_449_, 
order0_.TotalLines AS TotalLines449_, order0_.GrandTotal AS GrandTotal449_, order0_.M_Warehouse_ID AS M46_449_, order0_.M_PriceList_ID AS M47_449_, 
order0_.IsTaxIncluded AS IsTaxIn48_449_, order0_.C_Campaign_ID AS C49_449_, order0_.C_Project_ID AS C50_449_, order0_.C_Activity_ID AS C51_449_, 
order0_.Posted AS Posted449_, order0_.AD_User_ID AS AD53_449_, order0_.CopyFrom AS CopyFrom449_, order0_.DropShip_BPartner_ID AS DropShip55_449_, 
order0_.DropShip_Location_ID AS DropShip56_449_, order0_.DropShip_User_ID AS DropShip57_449_, order0_.IsSelfService AS IsSelfS58_449_, 
order0_.AD_OrgTrx_ID AS AD59_449_, order0_.User1_ID AS User60_449_, order0_.User2_ID AS User61_449_, order0_.Deliverynotes AS Deliver62_449_, 
order0_.C_Incoterms_ID AS C63_449_, order0_.Incotermsdescription AS Incoter64_449_, order0_.Generatetemplate AS Generat65_449_, 
order0_.Delivery_Location_ID AS Delivery66_449_, order0_.CopyFromPO AS CopyFromPO449_, order0_.FIN_Paymentmethod_ID AS FIN68_449_, 
order0_.FIN_Payment_Priority_ID AS FIN69_449_, order0_.RM_PickFromShipment AS RM70_449_, order0_.RM_ReceiveMaterials AS RM71_449_, 
order0_.RM_CreateInvoice AS RM72_449_, order0_.C_Return_Reason_ID AS C73_449_, order0_.RM_AddOrphanLine AS RM74_449_, 
order0_.A_Asset_ID AS A75_449_, order0_.Calculate_Promotions AS Calculate76_449_, order0_.C_Costcenter_ID AS C77_449_, 
order0_.Convertquotation AS Convert78_449_, order0_.C_Reject_Reason_ID AS C79_449_, order0_.validuntil AS validuntil449_, 
order0_.Quotation_ID AS Quotation81_449_, order0_.SO_Res_Status AS SO82_449_, order0_.Create_POLines AS Create83_449_, 
order0_.Iscashvat AS Iscashvat449_, order0_.RM_Pickfromreceipt AS RM85_449_, order0_.EM_APRM_AddPayment AS EM86_449_ 
FROM C_Order order0_ 
LEFT JOIN C_BPartner businesspa1_ ON order0_.C_BPartner_ID=businesspa1_.C_BPartner_ID 
CROSS JOIN C_DocType documentty2_ 
WHERE order0_.C_DocTypeTarget_ID=documentty2_.C_DocType_ID AND (upper(coalesce(to_char(businesspa1_.Name), '')) LIKE upper('%sold%') escape '|') 
AND order0_.IsSOTrx='Y' AND documentty2_.IsReturn='N' AND (documentty2_.DocSubTypeSO NOT LIKE 'OB') 
AND (order0_.AD_Org_ID IN ('93026108D6314843BDAF1E2C9582029E' , '52096B938D1B4A0BBED92CC51175D8B5' , 'C640F59345554138AA2278DA89B3D525' , '1860BD190DCC4A3BA8441B7D55178E61' , '16510591DECE46039236AD9E96D7FB55' , '994E4D2FA96A4C66B063E0A58E009481' , '4F5E7419E38A437B9381C738F4891E3F' , '0' , '951205728A984C9585E3C2179B4F2418' , 'A598837D7B0446649105844584E6565A' , 'C9040AD8D77642D98F89A5201E1EE1F5' , '190B2FF54907429591CE631C2A03BF24' , 'CFC24A4468734E5D9A05A7ACD63C78F5' , '725320F3DF8A4FD7A35F43B38CF7427C' , 'B9886DDD96124C5B8BE0F7BFADE5E3C3' , '9DE20F09DABA46BFA99DAFA52D9A6624' , '9C753DB213FA4F8B9FADA2136DFEF83E' , '77A6A6E722384AF884E80AC54D0E9A14' , 'A79072D955404CB3BC5F79C455560A62' , '19BB9330D92F4D12AA94DF1D26C1C643' , 'D448757A4607489C8915CDC5469CC1DD')) 
AND (order0_.AD_Client_ID IN ('7273232E589944E3B2C314CCFA24CE51' , '0')) 
ORDER BY order0_.DateOrdered DESC, order0_.C_Order_ID DESC 
LIMIT 100

Explan Plan (left join)

Limit  (cost=65106.88..65107.13 rows=100 width=3000)
 ->  Sort  (cost=65106.88..65109.36 rows=990 width=3000)
       Sort Key: order0_.dateordered, order0_.c_order_id
       ->  Hash Left Join  (cost=72.63..65069.04 rows=990 width=3000)
             Hash Cond: ((order0_.c_bpartner_id)::text = (businesspa1_.c_bpartner_id)::text)
             Filter: (upper((COALESCE(to_char(businesspa1_.name), ::character varying))::text) ~~ '%SOLD%'::text)
             ->  Hash Join  (cost=9.96..31758.78 rows=123712 width=3000)
                   Hash Cond: ((order0_.c_doctypetarget_id)::text = (documentty2_.c_doctype_id)::text)
                   ->  Seq Scan on c_order order0_  (cost=0.00..29954.99 rows=148455 width=3000)
                         Filter: (((ad_client_id)::text = ANY ('{7273232E589944E3B2C314CCFA24CE51,0}'::text[])) AND (issotrx = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{93026108D6314843BDAF1E2C9582029E,52096B938D1B4A0BBED92CC51175D8B5,C640F59345554138A (...)
                   ->  Hash  (cost=8.34..8.34 rows=130 width=32)
                         ->  Seq Scan on c_doctype documentty2_  (cost=0.00..8.34 rows=130 width=32)
                               Filter: (((docsubtypeso)::text !~~ 'OB'::text) AND (isreturn = 'N'::bpchar))
             ->  Hash  (cost=55.63..55.63 rows=563 width=49)
                   ->  Seq Scan on c_bpartner businesspa1_  (cost=0.00..55.63 rows=563 width=49)

Query plan when filtering with left join

Full query (inner join)

SELECT order0_.C_Order_ID AS C1_449_, order0_.AD_Client_ID AS AD2_449_, order0_.AD_Org_ID AS AD3_449_, order0_.IsActive AS IsActive449_, 
order0_.Created AS Created449_, order0_.CreatedBy AS CreatedBy449_, order0_.Updated AS Updated449_, order0_.UpdatedBy AS UpdatedBy449_, 
order0_.IsSOTrx AS IsSOTrx449_, order0_.DocumentNo AS DocumentNo449_, order0_.DocStatus AS DocStatus449_, order0_.DocAction AS DocAction449_, 
order0_.Processing AS Processing449_, order0_.Processed AS Processed449_, order0_.C_DocType_ID AS C15_449_, order0_.C_DocTypeTarget_ID AS C16_449_, 
order0_.Description AS Descrip17_449_, order0_.IsDelivered AS IsDeliv18_449_, order0_.IsInvoiced AS IsInvoiced449_, order0_.IsPrinted AS IsPrinted449_,
 order0_.IsSelected AS IsSelected449_, order0_.SalesRep_ID AS SalesRep22_449_, order0_.DateOrdered AS DateOrd23_449_, 
order0_.DatePromised AS DatePro24_449_, order0_.DatePrinted AS DatePri25_449_, order0_.DateAcct AS DateAcct449_, order0_.C_BPartner_ID AS C27_449_, 
order0_.BillTo_ID AS BillTo28_449_, order0_.C_BPartner_Location_ID AS C29_449_, order0_.POReference AS PORefer30_449_, 
order0_.IsDiscountPrinted AS IsDisco31_449_, order0_.C_Currency_ID AS C32_449_, order0_.PaymentRule AS Payment33_449_, 
order0_.C_PaymentTerm_ID AS C34_449_, order0_.InvoiceRule AS Invoice35_449_, order0_.DeliveryRule AS Deliver36_449_, 
order0_.FreightCostRule AS Freight37_449_, order0_.FreightAmt AS FreightAmt449_, order0_.DeliveryViaRule AS Deliver39_449_, 
order0_.M_Shipper_ID AS M40_449_, order0_.C_Charge_ID AS C41_449_, order0_.ChargeAmt AS ChargeAmt449_, order0_.PriorityRule AS Priorit43_449_, 
order0_.TotalLines AS TotalLines449_, order0_.GrandTotal AS GrandTotal449_, order0_.M_Warehouse_ID AS M46_449_, order0_.M_PriceList_ID AS M47_449_, 
order0_.IsTaxIncluded AS IsTaxIn48_449_, order0_.C_Campaign_ID AS C49_449_, order0_.C_Project_ID AS C50_449_, order0_.C_Activity_ID AS C51_449_, 
order0_.Posted AS Posted449_, order0_.AD_User_ID AS AD53_449_, order0_.CopyFrom AS CopyFrom449_, order0_.DropShip_BPartner_ID AS DropShip55_449_, 
order0_.DropShip_Location_ID AS DropShip56_449_, order0_.DropShip_User_ID AS DropShip57_449_, order0_.IsSelfService AS IsSelfS58_449_, 
order0_.AD_OrgTrx_ID AS AD59_449_, order0_.User1_ID AS User60_449_, order0_.User2_ID AS User61_449_, order0_.Deliverynotes AS Deliver62_449_, 
order0_.C_Incoterms_ID AS C63_449_, order0_.Incotermsdescription AS Incoter64_449_, order0_.Generatetemplate AS Generat65_449_, 
order0_.Delivery_Location_ID AS Delivery66_449_, order0_.CopyFromPO AS CopyFromPO449_, order0_.FIN_Paymentmethod_ID AS FIN68_449_, 
order0_.FIN_Payment_Priority_ID AS FIN69_449_, order0_.RM_PickFromShipment AS RM70_449_, order0_.RM_ReceiveMaterials AS RM71_449_, 
order0_.RM_CreateInvoice AS RM72_449_, order0_.C_Return_Reason_ID AS C73_449_, order0_.RM_AddOrphanLine AS RM74_449_, 
order0_.A_Asset_ID AS A75_449_, order0_.Calculate_Promotions AS Calculate76_449_, order0_.C_Costcenter_ID AS C77_449_, 
order0_.Convertquotation AS Convert78_449_, order0_.C_Reject_Reason_ID AS C79_449_, order0_.validuntil AS validuntil449_, 
order0_.Quotation_ID AS Quotation81_449_, order0_.SO_Res_Status AS SO82_449_, order0_.Create_POLines AS Create83_449_, 
order0_.Iscashvat AS Iscashvat449_, order0_.RM_Pickfromreceipt AS RM85_449_, order0_.EM_APRM_AddPayment AS EM86_449_ 
FROM C_Order order0_ 
INNER JOIN C_BPartner businesspa1_ ON order0_.C_BPartner_ID=businesspa1_.C_BPartner_ID 
CROSS JOIN C_DocType documentty2_ 
WHERE order0_.C_DocTypeTarget_ID=documentty2_.C_DocType_ID AND (upper(coalesce(to_char(businesspa1_.Name), '')) LIKE upper('%sold%') escape '|') 
AND order0_.IsSOTrx='Y' AND documentty2_.IsReturn='N' AND (documentty2_.DocSubTypeSO NOT LIKE 'OB') 
AND (order0_.AD_Org_ID IN ('93026108D6314843BDAF1E2C9582029E' , '52096B938D1B4A0BBED92CC51175D8B5' , 'C640F59345554138AA2278DA89B3D525' , '1860BD190DCC4A3BA8441B7D55178E61' , '16510591DECE46039236AD9E96D7FB55' , '994E4D2FA96A4C66B063E0A58E009481' , '4F5E7419E38A437B9381C738F4891E3F' , '0' , '951205728A984C9585E3C2179B4F2418' , 'A598837D7B0446649105844584E6565A' , 'C9040AD8D77642D98F89A5201E1EE1F5' , '190B2FF54907429591CE631C2A03BF24' , 'CFC24A4468734E5D9A05A7ACD63C78F5' , '725320F3DF8A4FD7A35F43B38CF7427C' , 'B9886DDD96124C5B8BE0F7BFADE5E3C3' , '9DE20F09DABA46BFA99DAFA52D9A6624' , '9C753DB213FA4F8B9FADA2136DFEF83E' , '77A6A6E722384AF884E80AC54D0E9A14' , 'A79072D955404CB3BC5F79C455560A62' , '19BB9330D92F4D12AA94DF1D26C1C643' , 'D448757A4607489C8915CDC5469CC1DD')) 
AND (order0_.AD_Client_ID IN ('7273232E589944E3B2C314CCFA24CE51' , '0')) 
ORDER BY order0_.DateOrdered DESC, order0_.C_Order_ID DESC 
LIMIT 100

Explain Plan (inner join)

Limit  (cost=28659.11..28659.36 rows=100 width=3000)
 ->  Sort  (cost=28659.11..28661.86 rows=1098 width=3000)
       Sort Key: order0_.dateordered, order0_.c_order_id
       ->  Hash Join  (cost=93.72..28617.15 rows=1098 width=3000)
             Hash Cond: ((order0_.c_doctypetarget_id)::text = (documentty2_.c_doctype_id)::text)
             ->  Nested Loop  (cost=83.76..28591.26 rows=1318 width=3000)
                   ->  Seq Scan on c_bpartner businesspa1_  (cost=0.00..199.20 rows=5 width=33)
                         Filter: (upper((COALESCE(to_char(name), ::character varying))::text) ~~ '%SOLD%'::text)
                   ->  Bitmap Heap Scan on c_order order0_  (cost=83.76..5650.61 rows=2224 width=3000)
                         Recheck Cond: ((c_bpartner_id)::text = (businesspa1_.c_bpartner_id)::text)
                         Filter: (((ad_client_id)::text = ANY ('{7273232E589944E3B2C314CCFA24CE51,0}'::text[])) AND (issotrx = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{93026108D6314843BDAF1E2C9582029E,52096B938D1B4A0BBED92CC51175D8B5,C640F59345554138A (...)
                         ->  Bitmap Index Scan on c_order_bpartner  (cost=0.00..83.40 rows=2224 width=0)
                               Index Cond: ((c_bpartner_id)::text = (businesspa1_.c_bpartner_id)::text)
             ->  Hash  (cost=8.34..8.34 rows=130 width=32)
                   ->  Seq Scan on c_doctype documentty2_  (cost=0.00..8.34 rows=130 width=32)
                         Filter: (((docsubtypeso)::text !~~ 'OB'::text) AND (isreturn = 'N'::bpchar))

Query plan when filtering with inner join

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Foreign_Key_Filtering_Performance_Improvement/QA"

This page has been accessed 1,912 times. This page was last modified on 4 August 2014, at 07:29. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.

Toolbox

PDF Books

Search

Participate

Projects:Foreign Key Filtering Performance Improvement/QA

Contents

Use Cases

With Standard Grid Configuration

1a) Filter by selecting a row in the filter drop down

2a) Filter by entering text manually in the filter editor

3a) Filter by selecting a row in the filter drop down, then modifying it manually

With allowFkFilterByIdentifier Grid Configuration set to false

1b) Filter by selecting a row in the filter drop down

2b) Filter by entering text manually in the filter editor

3b) Filter by selecting a row in the filter drop down, then modifying it manually

Performance Testing

Filtering by ID Vs Filtering by Identifier

Left Join Vs Inner Join

Query Plan Comparison in PostgreSQL 9.1

Filtering by ID Vs Filtering by Identifier

Full Query (filtering by identifier)

Explain Plan (filtering by identifier)

Full Query (filtering by id)

Explan Plan (filtering by id)

Filtering with 'left join' Vs Filtering with 'inner join'

Full query (left join)

Explan Plan (left join)

Full query (inner join)

Explain Plan (inner join)