Postgresql – Change to Postgres indexing scheme for IS NOT NULL?

PostgreSQL will generally only use one index per table. If you have single column indexes, it must choose only one. In your example, the query planner has to choose whether using the user_id, group_id or deleted_at index will be most performant. Which it chooses depends on the shape of your data, and whether your table statistics are up to date (run analyze my_table to make sure).

For example, if half the rows are deleted using an index on deleted_at would only reduce the number of rows to search by half. But if only a small fraction are in group 123, it will choose to use the index on group_id and then scan those for deleted_at is null and distinct user_id.

You can be more efficient about creating indexes for multiple columns by taking advantage of how composite indexes work. An index on (a, b, c) can cover queries which include a, queries which include a and b, and queries which include a, b, and c. It cannot cover queries which include only b or c.

For example, to cover every combination of deleted_at and group_id and user_id in you’d need three indexes.

• (deleted_at, user_id, group_id)
  • covers deleted_at, deleted_at + user_id, and deleted_at + user_id + group_id
• (group_id, deleted_at)
  • covers group_id, group_id + deleted_at
• (user_id, group_id)
  • covers user_id, user_id + group_id

But you said you had a lot of columns, so the number of indexes can expand rapidly. And since where deleted_at is null is likely to be used in most queries it makes more sense to partition your table by deleted_at is null. This will create two tables which appear to be one: one table has deleted rows, the other has active rows. If you include where deleted_at is null in a query PostgreSQL will simply query the appropriate partition, leaving it to choose indexes for other columns. It also makes it more efficient to remove "deleted" rows without blocking other queries.

You can’t partition an existing table, so you have to make a new table, partition it, and copy your data over.

The downside is if you have a primary key the partition key has to part of it. And nulls aren’t allowed in primary keys. So you’d have to change your strategy to use a special date like 9999-01-01. For convenience and safety, create a view which only selects non-deleted rows.

-- Move the old table out of the way.
alter table things rename to things_original;

-- Change the undeleted rows.
update things_original
set deleted_at = '9999-01-01' 
where deleted_at is null;

-- Create the new partitioned table
-- deleted_at must be part of the primary key
-- this allows IDs to be reused, which may or may not be what you want
create table things_all (
  id serial,
  name text not null,
  deleted_at timestamp default '9999-01-01',
  primary key(id, deleted_at)
) partition by list(deleted_at);

-- Make a partition for the active rows.
create table things_active partition of things_all for values in ('9999-01-01');

-- And one for the inactive rows.
create table things_deleted partition of things_all default;

-- Copy rows from the old table. They'll automatically be partitioned.
insert into things_all select * from things_original;

-- Add your indexes to things_all (not shown)

-- Create a view to only get active rows.
create view things as 
select * 
from things_all 
where deleted_at = '9999-01-01';

-- This will only query the things_active table, no deleted_at index required.
select * from things;

Demonstration