Alphabetical sort in postgresql using indexes.

Question

I have a table with more than 100000 rows and i want to sort them alphabetically using surname and name . I want to achieve the sort by using an index . I've tried this :

CREATE INDEX idx0 ON "People"(surname,name DESC NULLS LAST);

But the table is not sorted correctly . What am I doing wrong?

The index will speed up searches and order by. However, it will not re-order the physical table. I.e you still have to add an ORDER BY clause. — jarlh
– jarlh, Commented May 5, 2017 at 12:41
Tables represent unordered sets. If you want results in a particular order, you need to use order by on a query. — Gordon Linoff
– Gordon Linoff, Commented May 5, 2017 at 12:44
are you looking for CLUSTER command? keep in mind it is one time operation - later rows will unsort again — Vao Tsun
– Vao Tsun, Commented May 5, 2017 at 13:18

Marat Safin · Accepted Answer · 2017-05-06 17:33:50Z

2

If you want to select data from table in this order, you should execute a query:

SELECT * FROM People ORDER BY surname, name DESC NULLS LAST

This query will run fast, because database will use index instead of sorting data on the fly (note, that it works only if columns and sorting directions of query's ORDER BY matches exactly with columns and sorting directions of index).

If you want to reorder data in table physically, then you can execute:

CLUSTER People USING idx0

But keep in mind that CLUSTER command will only reorder existing data. If you insert new data into table, it will not be placed in desired order, so you will have to execute CLUSTER command again.

answered May 6, 2017 at 17:33

Marat Safin

1,92924 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Alphabetical sort in postgresql using indexes.

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related