3 ms·
Just index the less common value: CREATE INDEX ON session(is_active) WHERE is_active;
by dist-epoch 4y ago
Just index the less common value:
CREATE INDEX ON session(is_active) WHERE is_active;
- giovannibonetti 4y agoThere is no need for adding the boolean value to the index in this case, since it is constant (true). You can add a more useful column instead, like id or whatever your queries use: CREATE INDEX ON session(id) WHERE is_active;
- somehnguy 4y agoI tested that and it seemed to make 0 difference between a basic 'create index on table(column)'.
- klysm 4y agoI know nothing about partial indices in Postgres, but it seems like for indexing a Boolean, you either index the true or false values right? I feel like Postgres could intelligently choose to pick the less frequent value
- Someone 4y agoIs that correct? I would think that, even with NOT NULL Boolean field, the physical table has three kinds of rows: those with a true value, those with a false value, and those no longer in the table (with either true or false, but that doesn’t matter) If so, you can’t, in general, efficiently find the false rows if you know which rows have true or vice versa. You also can only use an index on rows with true values to efficiently find those with other values if the index can return the true rows in order (so that you can use the logic “there’s a gap in the index ⇒ there are non-true values in that gap)
- klysm 4y agoYeah that seems more like how it would work, I’m curious about the internals there
- deleted 4y ago[deleted]
- smilliken 4y agoThe benefit is a proportionally smaller index.
- dist-epoch 4y agoHave you measured the disk size of the index? That's where you should see a difference, not in speed.
- somehnguy 4y agoIt does appear smaller, but single digit megabytes on a table with millions of rows. Not a major difference for most use cases I think. But good to know for the few that it would make a difference.