From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject:
Date: 2014-06-06 22:15:53
Message-ID: CAOR=d=2y10X5+9-3uO7qQ=5ejCbPxZHPakf-iC8jZj2i-cwNpg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Well it's me again, with another performance regression. We have this query:

SELECT *
FROM users u
WHERE (u.user_group_id IN
(SELECT ug.id
FROM user_groups ug, pro_partners p
WHERE ug.pro_partner_id = p.id
AND p.tree_sortkey BETWEEN
E'0000000000010101000001000101000110000000000000000000000101101010'
AND
tree_right(E'0000000000010101000001000101000110000000000000000000000101101010')
OFFSET 0)
AND u.deleted_time IS NULL)
ORDER BY u.id LIMIT 1000;

OK so on 8.4.2 it runs fast. If I take out the offset 0 it runs slow.

If I run this on 8.4.15. 8.4.19 or 8.4.21 it also runs slow.

If I drop the limit 1000 it runs fast again. Query plans:

8.4.2 with offset 0: http://explain.depesz.com/s/b3G
8.4.2 without offset 0: http://explain.depesz.com/s/UFAl
8.4.2 without offset 0 and with no limit: http://explain.depesz.com/s/krdf
8.4.21 with or without offset 0 and no limit: http://explain.depesz.com/s/9m1
8.4.21 with limit: http://explain.depesz.com/s/x2G

A couple of points: The with limit on 8.4.21 never returns. It runs
for hours and we just have to kill it. 8.4.2 without the offset and
with a limit never returns. Tables are analyzed, data sets are the
same (slony replication cluster) and I've tried cranking up stats
target to 1000 with no help.

tree_sortkey is defined here: http://rubick.com/openacs/tree_sortkey
but I don't think it's the neus of the problem, it looks like join
estimations are way off here.

--
To understand recursion, one must first understand recursion.

Responses

  • Re: at 2014-06-07 01:38:54 from Tom Lane

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2014-06-07 01:38:54 Re:
Previous Message Merlin Moncure 2014-06-06 21:32:09 Re: CPU load spikes when CentOS tries to reclaim 'cached' memory