1

I've just found an interesting partial index in my db:

CREATE INDEX orders_idx
  ON orders
  USING btree
  (status)
  WHERE status IS NULL;

As you see it's completely non-selective and imho completely not usefull, or am I missing something?

Borys
  • 2,676
  • 2
  • 24
  • 37
  • 6
    Depends on how many rows have `NULL` in the status column. If there are only a few, this could be helpful for a `where status is null` condition. –  Mar 25 '14 at 14:17
  • @a_horse_with_no_name: That should pass as answer. – Erwin Brandstetter Mar 25 '14 at 15:54
  • 1
    Also: If all your queries that need an index on `status` have the WHERE condition `status IS NULL`, the index is useful even for a big number of NULLs - as long as its not the vast majority of rows. – Erwin Brandstetter Mar 25 '14 at 16:19
  • the amount of nulls in status column is decreasing. i need to run some more tests on that. thank you for your answers. – Borys Mar 26 '14 at 19:53

1 Answers1

0

If you have queries where you filter by status is null, (as mentioned in the comments,) or need to run maintenance on the entries where the status is null, this index may be useful.

For example from a project I used to work on (that didn't have this type of index,) I had an import process that keeps a log/queue. It sets status to queued when starting, running while in progress, and sets status to complete when finished - but sets it to null if there is an error. That will need to be cleaned up, because items in other tables will not have been correctly set to the values needed. I needed to routinely query on status is null to diagnose specific problems, or run cleanup scripts so the database wasn't full of partial imports.

David Manheim
  • 2,553
  • 2
  • 27
  • 42
  • yes, it's similar situation in my case. I was wondering if index maintance in this case isn't too expensive comparing to search cost. – Borys Mar 26 '14 at 19:50
  • 1
    It's the kind of thing you need to profile, I think. In our case, I think it was better to have the fixed overhead for index maintenence, to reduce the very heavy load that otherwise hit when we did cleanup, which would break things otherwise. (It was not a great system, in some ways.) – David Manheim Mar 27 '14 at 14:30