4 ms·
I tested that and it seemed to make 0 difference between a basic 'create index on table(column)'.
by somehnguy 3y ago
I tested that and it seemed to make 0 difference between a basic 'create index on table(column)'.
- klysm 3y 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 3y 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 3y agoYeah that seems more like how it would work, I’m curious about the internals there
- deleted 3y ago[deleted]
- smilliken 3y agoThe benefit is a proportionally smaller index.
- dist-epoch 3y agoHave you measured the disk size of the index? That's where you should see a difference, not in speed.
- somehnguy 3y 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.