-1

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?

5
  • Will your queries use other predicates? Aug 22, 2014 at 21:07
  • 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, 2014 at 21:28
  • What columns will you select from the table, and is there any order by or group by? Aug 22, 2014 at 21:31
  • No order by or group by, and I'll be SELECT *ing.
    – Ben
    Aug 22, 2014 at 21:49
  • 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? Aug 22, 2014 at 23:48

2 Answers 2

4

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:

1
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.