So are you suggesting as a general rule then that sub-queries are the way to force a specific join order in postgres? If that is the case, I will do this from now on.
I'll try to explain a bit better... Here's your original query :
select s.*, ss.* from shipment s, shipment_status ss, release_code r where s.current_status_id = ss.id and ss.release_code_id = r.id and r.filtered_column = '5' order by ss.date desc limit 100;
If you write something like :
SELECT * FROM shipment_status WHERE release_code_id = constant ORDER BY release_code_id DESC, date DESC LIMIT 100;
In this case, if you have an index on (release_code_id, date), the planner will use a limited index scan which will yield the rows in index order, which will be very fast.
However, if you just have an index on date, this won't help you.
In your case, moreover, you don't use release_code_id = constant, but it comes from a join. So there may be several different values for release_code_id ; thus the planner can't use the optimization, it has to find the rows with the release_code_id first. And it can't use the index on (release_code_id, date) to get the rows in sorted order precisely because there could be several different values for the release_code_id. And then it has to sort by date.
I hope this makes it clearer. If you are absolutely sure there is only one row in release_code with r.filtered_column = '5', then this means release_code_id is a constant and your query could get a huge speedup by writing it differently.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
