issue with double ordering in a wrapped distinct - Mailing list pgsql-general

I have a particular query that returns resultset of 45k rows out of a large resultset (pg 9.3 and 9.1)

It's a many 2 many query, where I"m trying to search for Bar based on attributes in a linked Foo.

I tweaked the indexes, optimized the query, and got it down an acceptable speed around 1,100ms

the second I added a limit/offset though -- the query plan completely changed and it ballooned up to 297,340 ms.   Yes,
Iwaited that long to see what was going on in the query planner. 

I did a lot of playing around, and managed to get this form of a query to work in 305ms with a limit/offset.

SELECT DISTINCT qinner.bar_id
FROM
  (SELECT foo_2_bar.bar_id AS bar_id
   FROM foo_2_bar
   JOIN foo ON foo_2_bar.foo_id = foo.id
   WHERE foo.biz_id = 1
     AND (foo.is_hidden IS NOT TRUE)
   ORDER BY foo_2_bar.bar_id ASC
   ) AS qinner
ORDER BY qinner.bar_id ASC
LIMIT 100
OFFSET 0
;

This is what I don't understand -- notice the two order_by calls.

    If i run this with an inner and outer order_by, I get ~305ms.  (I don't think I need both, but I wasn't sure if
orderingis kept from a subselect ) 

    If i run this with only the inner, I get ~304ms.

    If I run this with only the outer, it's pushing over 10minutes again

i'm wondering if anyone might know why that performance hit would be happening