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?
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:
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.
