Performance: Database Index on invoice_line_items.product_id

Version: 1.0.51
Category: Database / Performance

Overview

Version 1.0.51 adds a database index on the invoice_line_items.product_id column. This is a targeted performance improvement for the topProducts query, which joins against this column on every execution.

Background

The topProducts query is used to identify the best-performing products by revenue and volume. It joins the invoice_line_items table on the product_id foreign key. Prior to this release, no index existed on invoice_line_items.product_id, meaning every execution of this query triggered a sequential scan — reading every row in the table regardless of how many rows were actually relevant.

Sequential scans on large tables are a common source of latency in analytics workloads. As the invoice_line_items table grows with each new order, the cost of this scan grows linearly.

What Was Added

A B-tree index on invoice_line_items.product_id:

CREATE INDEX ON invoice_line_items (product_id);

This allows the database query planner to switch from a sequential scan to an index scan when filtering or joining on product_id, reducing query time from O(n) to O(log n + k) where k is the number of matching rows.

Affected Queries

Query	Table	Join Column
topProducts	invoice_line_items	product_id

Who Benefits

• Operations dashboards that surface top product performance metrics.
• Revenue analytics pages that rank products by invoice totals.
• Any automated report generated by the platform that calls the topProducts query internally.

Notes

• This is a non-breaking, additive change. No application code or API behaviour is altered.
• The index will be built automatically as part of the database migration included in this release.
• For very large existing deployments, the index build may take a short amount of time to complete on first migration run.