Name: [PERF] pos_sale: optimize Session Sale Amount compute by pre-fetching

State: Killed finished in 250m

PR State: merged

PR Author: Roy Le

PR Author Email:

PR: #1156

Committer: Roy Le

Committer Email: roy.le@viindoo.com

Commit: 868881f8de1b96c6b1e0ed1223adb1967dc16217

Description:

                                            [PERF] pos_sale: optimize Session Sale Amount compute by pre-fetching
opened sessions to avoid heavy subquery execution

Problem

The original implementation performs a _read_group on report.pos.order
with a domain that includes a relational lookup:

('session_id.state', '=', 'opened')

On large databases, this condition forces the ORM to generate a SQL
query containing nested subqueries like:

session_id IN (SELECT id FROM pos_session WHERE state='opened' AND ...)

PostgreSQL cannot short-circuit this subquery, even if no sessions are
opened, and still scans a large portion of report_pos_order, causing
significant performance degradation.

Solution

Instead of letting _read_group build a heavy subquery, we explicitly
search for the opened sessions first:

opened_sessions = self.env['pos.session'].search([
    ('config_id.crm_team_id', 'in', self.ids),
    ('state', '=', 'opened'),
])

data = self.env['report.pos.order']._read_group([
    ('session_id', 'in', opened_sessions.ids),
], ['config_id'], ['price_total:sum'])

This improves performance because:

pos.session is a much smaller table and is fast to filter.

If there are no opened sessions, _read_group immediately returns empty
results without scanning report_pos_order.

It eliminates nested SQL IN (SELECT ...), allowing PostgreSQL to use
direct index-based filtering.

This approach reduces query time dramatically on large datasets.
                                            

Branch: 17.0

Instance ID: 0

Age:

Up-time: