Thread:
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.
Scott Marlowe <scott.marlowe@gmail.com> writes:
> 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.
This seems to be about misestimation of the number of rows out of a
semijoin, so I'm thinking that the reason for the behavior change is
commit 899d7b00e9 or 46f775144e. It's unfortunate that your example
ends up on the wrong side of that change, but the original 8.4.x behavior
was definitely pretty bogus; I think it's only accidental that 8.4.2
manages to choose a better plan. (The fact that you need the crutch
of the "OFFSET 0" to get it to do so is evidence that it doesn't
really know what its doing ;-).)
One thing you might try is back-patching commit 4c2777d0b733, as I
suspect that you're partially getting burnt by that in this scenario.
I was afraid to back-patch that because of the API change possibly
breaking third-party code, but in a private build that's unlikely
to be an issue.
regards, tom lane
Thanks we'll give that a try. On Fri, Jun 6, 2014 at 7:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Scott Marlowe <scott.marlowe@gmail.com> writes: >> 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. > > This seems to be about misestimation of the number of rows out of a > semijoin, so I'm thinking that the reason for the behavior change is > commit 899d7b00e9 or 46f775144e. It's unfortunate that your example > ends up on the wrong side of that change, but the original 8.4.x behavior > was definitely pretty bogus; I think it's only accidental that 8.4.2 > manages to choose a better plan. (The fact that you need the crutch > of the "OFFSET 0" to get it to do so is evidence that it doesn't > really know what its doing ;-).) > > One thing you might try is back-patching commit 4c2777d0b733, as I > suspect that you're partially getting burnt by that in this scenario. > I was afraid to back-patch that because of the API change possibly > breaking third-party code, but in a private build that's unlikely > to be an issue. > > regards, tom lane -- To understand recursion, one must first understand recursion.