Query Performance - on archived vs unarchived

[cthornbe]

We have run into issues in our system which utilizes Postgres for data storage. When we attempt to get a list of objects which are unarchived or archived the queries are unable to hit an index. Digging in what we've discovered is that Postgres does NOT index NULL values.

When we make a query for the Object.unarchived.explain we see it's doing a seq scan on the table

                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on objects  (cost=0.00..75.03 rows=1403 width=549)
   Filter: ((archived_at IS NULL) AND (archive_number IS NULL))
(2 rows)

This is with an index added for archived_at and archive_number.

Has anyone else run into this? If so, were there any resolutions?

What is the thought of changing the gem to be able to use "another" default value instead of nil for those properties? For example:

archived_at = '1980-01-01 00:00:00'
archive_number = 0

This way Postgres would be able to index those values?

We've tried different partial indexes and the Object.archived is able to hit the index BUT the one for Object.unarchived still does the seq scan.

Thoughts?

UPDATE: It appears the issue is the scope of checking both archived_at and archive_number is the problem in that Postgres does NOT like checking for 2 null values. Theoretically, you could move from checking one column to both columns and performance would improve.