0049617: performance imporovement on the ad_session table

Description

On environment with lot of concurrency and lot of external calls to the server we have identified a query that is executed continuously and reading lot of information from the disk.

The query is the following:
select max(adsession0_.Created) as col_0_0_ from AD_Session adsession0_ where adsession0_.Username=$1 and adsession0_.Login_Status<>'F'

Adding an index (username,login_status) on the ad_session table improves the performance across the System during API calls. Noticed this while analyzing the query performance in CSU customers. Contact Egoitz if there are any doubts.

Steps To Reproduce

Having pg_stat_statements installed on the database of a customer with lot of concurrencies, we have taken some statistics about the information read.
The result is that for the query on the ad-sesion table, many TB of disk are readed.
The data read per execution is 788MB but as the query is executed many times, the accumulated data read is really high.

To enable pg_stat_statemnts:
http://wiki.openbravo.com/wiki/Finding_Perfomance_Issues#Tracking_SQL_Statements [^]

Proposed Solution

Add the index (username,login_status) on the ad_session

Tags

NOR

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0140153) hgbot (developer) 2022-08-16 13:23	Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/683 [^]

(0140214) hgbot (developer) 2022-08-18 08:30	Directly closing issue as related merge request is already approved. Repository: https://gitlab.com/openbravo/product/openbravo [^] Changeset: 0fd2e4cdc7ab25602fd6490b079840fafe8fae7d Author: adrian.blasco <adrian.blasco@openbravo.com> Date: 18-08-2022 08:27:29 URL: https://gitlab.com/openbravo/product/openbravo/-/commit/0fd2e4cdc7ab25602fd6490b079840fafe8fae7d [^] Fixes ISSUE-49617 Performance improvement. New Index ( username ) in ad_session table A new index has been created (ad_session_username_idx) that helps with the performance of a query that is executed each time a user tries to log in --- M src-db/database/model/tables/AD_SESSION.xml ---

(0140215) hgbot (developer) 2022-08-18 08:30	Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/683 [^]

Issue History
Date Modified	Username	Field	Change
2022-06-21 13:43	shankarb	New Issue
2022-06-21 13:43	shankarb	Assigned To	=> Triage Platform Base
2022-06-21 13:43	shankarb	Modules	=> Core
2022-06-21 13:43	shankarb	Triggers an Emergency Pack	=> No
2022-06-21 13:58	shankarb	Description Updated	View Revisions
2022-06-21 13:58	shankarb	Steps to Reproduce Updated	View Revisions
2022-06-22 14:38	rafaroda	Tag Attached: NOR
2022-07-18 09:45	egoitz	Severity	minor => major
2022-07-18 09:45	egoitz	Summary	Index in session table => performance imporovement on the ad_session table
2022-07-18 09:45	egoitz	Description Updated	View Revisions
2022-07-18 09:45	egoitz	Steps to Reproduce Updated	View Revisions
2022-08-16 13:23	hgbot	Note Added: 0140153
2022-08-18 08:30	hgbot	Resolution	open => fixed
2022-08-18 08:30	hgbot	Status	new => closed
2022-08-18 08:30	hgbot	Fixed in Version	=> PR22Q4
2022-08-18 08:30	hgbot	Note Added: 0140214
2022-08-18 08:30	hgbot	Note Added: 0140215