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, I waited 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 ordering is 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
