3 ms·
You will find major performance improvements by using `include` with your indexes. For instance, on something trivial like counting votes on a blog post:
by VWWHFSfQ 2y ago
You will find major performance improvements by using `include` with your indexes. For instance, on something trivial like counting votes on a blog post:
create table post_vote (
post_id bigint not null references posts (id),
user_id bigint not null references users (id),
amount smallint not null,
primary key (post_id, user_id) include (amount)
);
select sum(amount) from post_vote where post_id = :'post_id';
Upvotes are 1, downvotes are -1. This will be a nearly instantaneous index-only scan summing the amount values.
- levkk 2y agoTIL. Thanks!
- cwbriscoe 2y agoDownside being that updates to amount are more costly and more storage required for the index. In your example though, it is probably a good optimization since how often do you change your up/down votes. I just wanted to point out that you really have to think about these types of optimizations because they might make one query faster but slow everything else down to the point where you are spending more time overall and using more storage.
- VWWHFSfQ 2y ago> Downside being that updates to amount are more costly PG always does a delete + create for updates. It will have no additional overhead at all
- cwbriscoe 2y agoYou are updating both the data and the index when updating amount instead of just the data. That is twice the number of IO's.
- VWWHFSfQ 2y agoIt's the same IOs. PG does it regardless. Both the data and index
- Izkata 2y agoI believe they're confusing a single index with more columns vs adding additional indexes. Additional indexes would be slower to update. I suppose additional columns could be slower to update too (more data structure traversal), but I'm guessing not by nearly as much as an additional index.