When you run a search in a database that combines multiple columns (e.g. (unit price × quantity - discount) × tax rate ), it usually triggers a full scan (Table Scan) and causes major slowdowns.
In this post, I'll explain how to roll the calculated result of multiple columns into a single or composite index, along with how big the write overhead (latency risk) you're probably worried about actually gets, and what to do when you need to be careful.
1. Two ways to put the calculated result of multiple columns into an index
For example, consider an orders table with these 5 columns:
price (unit price)
quantity (quantity)
discount (discount)
tax_rate (tax rate)
status (order status)
If you want to quickly find "completed orders with a total billed amount of 10,000 yen or more," there are 2 approaches.
Approach A: Create an expression index directly
Define the formula itself in the index, and create a composite index that combines it with the status column.
SQL (PostgreSQL)
CREATE INDEX…