Menu

Indexing Calculated Results from Multiple SQL Columns, and the Limits of Write Overhead
📰

Indexing Calculated Results from Multiple SQL Columns, and the Limits of Write Overhead

DEV Community: rails·Kaziu·about 6 hours ago
#jP1ywnI5
#devtorails#devto#dev#index#status
Reading 0:00
15s threshold

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…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.