This is not true in general: there are circumstances where Postgres can skip updating indexes when a tuple is updated, assuming the update only changes values in non-indexed columns. See:
http://pgsql.tapoueh.org/site/html/misc/hot.html
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...
Every time a new order is created the status flows from
* unassigned
* assigned
* passenger on board
* completed
That should really be an event stream, but if you want to find all `unassigned` orders, an index would be good.
We had a similar problem with this kind of table, at much lower volume than uber.
But only when there was a two column index
`status, pickup time`
Or
`status, created at`
No, an index is not good for that, because it only has 4 values (at most 4 times faster than a full table scan), and it hammers the index on every update, using PostgreSQL or not.
Same for the two column index: single column indexes on pickup time and creation time are highly selective, and their values are updated basically never, whereas status has low selectivity and updates are common: combining them you don't significantly improve selects on the two columns, and limit the performance edge on time select where time.
Status index only performs well when you select one of the values which is is significantly underrepresented in the column (e.g. all except completed). The most performing approach without doing black magic is to split in columns with separate indexes: Assigned Time, Pickup Time, Completed Time. If you want unassigned ones, you select where Assigned Time IS NULL, same for the other columns. Performance on update for most RDBMS blasts, as index updates are now distributed among several, allowing higher concurrency and overall throughput.