From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | issue with double ordering in a wrapped distinct |
Date: | 2014-11-18 23:35:44 |
Message-ID: | 9080F974-DFCB-4137-BF98-77B59533EF97@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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, 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
From | Date | Subject | |
---|---|---|---|
Next Message | John Smith | 2014-11-18 23:37:11 | how to delay sync by a set time, hour or day? |
Previous Message | Kevin Grittner | 2014-11-18 20:35:20 | Re: String searching |