Postgres DESC index on date field

Question

I have a date field on a large table that I mostly query and sort in DESC order. I have an index on that field with the default ASC order. I read that if an index is on a single field it does not matter if it is in ASC or DESC order since an index can be read from both directions. Will I benefit from changing my index to DESC?

Check the execution plan (use explain analyze) and you'll know. — user330315
– user330315, Commented Jan 5, 2015 at 15:14
You probably read here: Since an ordered index can be scanned either forward or backward, it is not normally useful to create a single-column DESC index — that sort ordering is already available with a regular index. postgresql.org/docs/current/static/… — pozs
– pozs, Commented Jan 5, 2015 at 15:38

zerkms · Accepted Answer · 2015-01-06 00:04:57Z

3

operating systems are generally more efficient reading files in a forwards direction, so you may get a slight speed up by creating a DESC index.

For a big speed up create the DESC index and CLUSTER the table on it.

CLUSTER tablename USING indexname;

clustering on the ASC index will also give improvement, but it will be less.

edited Jan 6, 2015 at 0:04

zerkms

256k73 gold badges447 silver badges551 bronze badges

answered Jan 5, 2015 at 20:50

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Chan Over a year ago

I am dubious of the claim that sequential file handling by the OS will somehow affect the speed of a single-column index. It is after all handled as a binary search which does random access anyway. Do you have any docs or sources on this matter? I would be very interested

Jasen Over a year ago

disk drives are faster for sequential reads, anything that promotes sequential reads will engance performance. if the table is and expected result set is large enough that an index scan is chosen for the query haveing the part of the query that reads the table do so mostly in a sequential order is going to boost performance.

Collectives™ on Stack Overflow

Postgres DESC index on date field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related