Postgresql index design: combined indexes ordered

Question

I know that if I have two queries like:

SELECT * FROM fruits WHERE type='apple'

and

SELECT * FROM fruits WHERE type='orange' AND state='rotten'

then an effective index would be:

CREATE INDEX type_state_index_on_fruits ON fruits(type, state) USING btree;

Since it would be able to use the index for both the first and the second query.

However what happens if there is sorting going into this:

SELECT * FROM fruits WHERE type='orange' AND state='rotten' ORDER BY picked_at ASC

Can I effectively create an ordered index of type and state but ordered by picket_at, so that the database would not have to do the sorting after fetching row in both cases:

SELECT * FROM fruits WHERE type='apple' ORDER BY picked_at ASC
SELECT * FROM fruits WHERE type='orange' AND state='rotten' ORDER BY picked_at ASC

Answer 1

If you want one index for both queries, I'd recommend one on (type, picked_at).

If you need to have state in the index, you'll probably need two indexes.