Re: best index for timestamp field null and not null queries

On 26/08/12 19:08, Jeremy Wells wrote:
> Hi, I've got a database table with a datetime column "deleted_at".
> I'll be running lots of queries against the table but they'll all be
> of the nature "deleted_at IS NULL" and "deleted_at IS NOT NULL".
>
> What's the best index to place on this column? I assume if I add just
> a straight index then it'll index all of the values that go into it.

Not tested!
How about the following partial index?
*
CREATE INDEX**deleted_at_is_null ON my_table WHERE deleted_at IS NULL*;
*
**SELECT count(*) FROM my_table WHERE deleted_at IS NULL;*

The index should be substantially smaller than one storing dates, as the field
will be small and you will have fewer rows.

If the NULL value is the most common, then use the predicate
*deleted_at IS NOT NULL
*(and change the index name accordingly!).

Cheers,
Gavin