Re: Performance problem with semi-large tables

> Well, postgres does what you asked. It will be slow, because you have a
> full table join. LIMIT does not change this because the rows have to be
> sorted first.

I am aware that limit doesn't really affect the execution time all that
much. It does speed up ORM though and keeps the rows to a manageable list
so users don't have to look at thousands, which is good enough for me. My
intention here is that the date was supposed to be a good filter.

> The date is in shipment_status so you should first get the
> shipment_status.id that you need and later join to shipment. This will
> avoid the big join :
>
>
> SELECT s.*, ss.* FROM
> (SELECT * FROM shipment_status WHERE release_code_id IN
> (SELECT r.id FROM release_code WHERE r.filtered_column = '5')
> ORDER BY date DESC LIMIT 100
> ) as ss, shipment s
> WHERE s.current_status_id = ss.id
> ORDER BY date DESC LIMIT 100
>
> Is this better ?

This looks like it might be what I want. It's not that I was not aware of
the correct join order. I used Dan Tow's diagram method and learned that
filtering on date first is the best approach, then releae code, then finally
shipment for this particular query. I just didn't know how to tell
PostgreSQL how to do this.

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.