issue with double ordering in a wrapped distinct

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

Responses

Browse pgsql-general by date

  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