3 ms·
What is the performance impact on INSERT when using tables with complex indices like the one mentioned in the article?
by justusw 10y ago
What is the performance impact on INSERT when using tables with complex indices like the one mentioned in the article?
- coltonv 10y agoI'm not the author but I'd guess almost nothing. Basically you just need to calculate the result of the function and index it by the result, should be almost completely unnoticeable.
- zerd 10y agoUnless it's a table with high write load. You're basically shifting the expensive part from read time to write time.
- malisper 10y agoI'm maintaining a database cluster with 10s of millions of partial indexes, many of which contain a LIKE pattern. Each table in the cluster has several of these partial indexes. Because of all of these partial indexes, we became CPU bound. For the longest time, we thought all of the CPU was going to evaluating the LIKE patterns. Eventually, I decided to generate a flame graph of our database and discovered that we were only spending 0.3% of the time evaluating the partial index predicates. In turns out most of the CPU usage was evenly split between looking up the metadata of the index and in parsing the index predicate. So the index expression itself doesn't really matter, but having lots of these indexes can become an issue.