4 ms·
Does this principle apply if you have one or a few values that are very common? E.g an integer column with 90% of them being 0?
by pletnes 3y ago
Does this principle apply if you have one or a few values that are very common? E.g an integer column with 90% of them being 0?
- ChristianGeek 3y agoOnly if you used null to represent the common value, which generally isn’t a good idea.
- convivialdingo 3y agoAn index is most efficient when it represents a unique set of values. It's still very useful to have an index for grouping, but if the groups represent a severe minority then you will end up wasting a lot of space and cycles searching through the index.
- teddyh 3y agoCouldn’t you create separate indexes for the 0 and non-0 cases?
- recursive 3y agoIf the zeroes are most of the common value, it could be slower to use the index when searching for zeroes. How so? Reading values that aren't included in the index require following a reference back to the table row. If 90% of the rows in your query are zeroes, you'd be better of not using that column in your query planning. A naive filter, possibly even after a table scan, is likely to be faster than using the index.
- lozenge 3y agoYes, Postgres keeps track of the most common values. If it knows 0 is common and "where val = 0" will keep 90% of rows it might choose to use a table scan instead of the index.