Indexing into two "buckets"

Question

I'm creating a database with PostgreSQL that has an integer field called "delay". All of my queries on this column will be checking if the value of this column is less than or equal to 0, or greater than 0. Is there anything I can do to increase the speed of access on this column? I currently believe that the best I can do is:

CREATE INDEX index_delay ON tablename (delay ASC);

Is this the best that I can do, or is there a more efficient index for this use case?

No. The only queries I can currently think of for this are ones that are exclusively WHERE delay>0 or WHERE delay<=0. — Ben, Aug 22 '14 at 21:28
What columns will you select from the table, and is there any order by or group by? — Lennart - Slava Ukraini, Aug 22 '14 at 21:31
Obviously, you need to provide more information. Your version of Postgres? How many rows? Typical access patterns? Are rows updated a lot / is delay updated a lot? What percentage of rows has delay < 0? Typical query? Table definition? — Erwin Brandstetter, Aug 22 '14 at 23:48

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

Going out on a limb here (basic information is missing), partial indexes will probably be your best bet.
Much easier to handle than partitioning the whole table, it offers similar performance for the split case and allows much better performance for queries on the whole table:

CREATE INDEX tbl_nodelay_idx ON tbl (tbl_id, ??) WHERE delay <= 0;
CREATE INDEX tbl_delay_idx ON tbl (tbl_id, ??) WHERE delay > 0;

Which columns to include and other details depend on the missing details in the question.

Related answers with more details:

That's pretty cool, I wasn't aware of that. Thanks – Lennart - Slava Ukraini Aug 23 '14 at 00:23 — Lennart - Slava Ukraini, Aug 23 '14 at 00:23

score 0 · Answer 2 · answered Aug 22 '14 at 22:43

Given the information in your comments there's not much you can do index-wise beside the index that you proposed. This index will have poor selectivity.

Dependent of the size of the table and the update frequency of delay you might consider range partitioning, one partition for delay > 0 and one partition for delay <= 0. Instead of scanning the index and fetch remaining columns, the query can scan the partition that match the predicate.

Indexing into two "buckets"

2 Answers2