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.