0

A very practical example:

Assume an User table that has a "deleted" column that stores timestamp. Assume that there is a "deleted IS NOT NULL" filter on practically every query that accesses this table. This field is very very rarely not null, and when it is it's usually very short-lived.

Questions:

  • Is there an index necessary? While the entropy of this column is usually 1, I don't think that Postsgresql keeps track of the entropy without creating an index, which means it'd need to do a full table scan for each query (probably as the last operation, but still ouch!)
  • Does it make sense to pre-optimize this index to be a Hash Index instead of a B-index, assuming I don't need all the additional operators from the B-index? Since entropy is close to 1, I don't think there is actually a major size difference between both of the indexes, and thus a B-Index would be superior.
5
  • 1
    I would focus on what other columns are commonly used to query the table. If deleted is only NULL, let's say, 1% of the time. If your query is for NOT NULL, the index is only going to help you eliminate 1% of your result set. Mar 4, 2022 at 15:10
  • What worries me are situations where the entire table might be the result set, and that is the only applied filter.
    – Joe
    Mar 4, 2022 at 15:31
  • 1
    I let someone who has more knowledge of Postgress provide an answer. However, I can't picture an index helping you in this situation. Indexes work be reducing the amount of data to be read. If you're still going to return 99% of the table, you still need to read that 99%. It's a very minimal gain, if any. Mar 4, 2022 at 15:53
  • 2
    @BrendanMcCaffrey & Joe Actually I can still see one advantage of an index even when returning the entire table, and that's in the case where the data needs to be returned ordered by the same fields that the index is defined on. The index can save you from a sort operation needing to occur because the B-Tree already persists the data in the appropriate order. With the right amount of data, this is not a trivial savings either, so it could be useful. But that's a much smaller subset of use cases where one would solely create an index just to save on sorting.
    – J.D.
    Mar 4, 2022 at 21:28
  • 1
    Good point J.D. I was not thinking about it in that regard. Mar 4, 2022 at 21:36

1 Answer 1

2

No, an index on deleted would not be helpful, because the condition eliminates too few rows to warrant an index scan.

But if all your queries contain WHERE deleted IS NOT NULL, you could create all your indexes with the same WHERE condition. Such “partial indexes” can be used with these queries, and they automatically exclude rows that don't satisfy that condition from the result set. So you have indexed the condition without creating an extra index!

1
  • Smart! I didn't even consider putting the condition on the existing indexes. Great idea!
    – Joe
    Mar 11, 2022 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.