issue with double ordering in a wrapped distinct
От | Jonathan Vanasco |
---|---|
Тема | issue with double ordering in a wrapped distinct |
Дата | |
Msg-id | 9080F974-DFCB-4137-BF98-77B59533EF97@2xlp.com обсуждение исходный текст |
Ответы |
Re: issue with double ordering in a wrapped distinct
Re: issue with double ordering in a wrapped distinct |
Список | 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
В списке pgsql-general по дате отправления: