[rt-devel] postgres query optimising (was: Re: [rt-users] Is this slow? It seems slow.)
Jamie Wilkinson
jaq at spacepants.org
Wed Jun 11 02:47:44 EDT 2003
[taken to -devel]
This one time, at band camp, Jamie Wilkinson wrote:
>If that's true, could it be that a UNION query would be much better,
>allowing the database to perform each of these queries separately and fast,
>and then composing the results together?
For example, taking the bottom part of the query regarding Principals_3, we
have:
(
(
ACL_4.PrincipalId = Principals_3.id
AND
Principals_3.id = Groups_2.id
AND
ACL_4.PrincipalType = 'Group'
AND
(
Groups_2.Domain = 'SystemInternal'
OR
Groups_2.Domain = 'UserDefined'
OR
Groups_2.Domain = 'ACLEquivalence'
)
)
OR
(
-- principal is in group for the current queue or this exact ticket
(
(
Groups_2.Domain = 'RT::Queue-Role'
AND
Groups_2.Instance = 5
)
OR
(
Groups_2.Domain = 'RT::Ticket-Role'
AND
Groups_2.Instance = 388
)
)
AND
Groups_2.Type = ACL_4.PrincipalType
AND
Groups_2.id = Principals_3.id
AND
Principals_3.PrincipalType = 'Group'
)
)
Now, just that part as a complete SQL query looks like:
select distinct principals.* from
acl, principals, groups
where (
acl.principalid = principals.id
and
principals.id = groups.id
and
acl.principaltype = 'Group'
and
groups.domain in ('SystemInternal', 'UserDefined', 'ACLEquivalence')
) or (
(
(groups.domain = 'RT::Queue-Role' and groups.instance = 5)
or
(groups.domain = 'RT::Ticket-Role' and groups.instance = 388)
)
and
groups.type = acl.principaltype
and
groups.id = principals.id
and
principals.principaltype = 'Group'
);
(I'm using the IN syntax in place of the OR above, for conciseness --
there's no speed difference for such a short number of options)
This query returns about 63 rows in my database, with a SELECT DISTINCT it
returns 12. The query itself takes about 6.5 seconds to complete, mainly
due to the OR in the middle making postgres perform a large join (see
attachment rt-or-query).
Now, I write the two parts of the OR separately, and join them together
using UNION. This has the effect of not asking postgres to have each part
depend on the other, so it can do each part quickly, and then it merges the
results together, without duplicates ala the SELECT DISTINCT.
select principals.* from
acl, principals, groups
where
acl.principalid = principals.id
and
principals.id = groups.id
and
acl.principaltype = 'Group'
and
groups.domain in ('SystemInternal', 'UserDefined', 'ACLEquivalence')
union
select principals.* from
groups, acl, principals
where
(
(groups.domain = 'RT::Queue-Role' and groups.instance = 5)
or
(groups.domain = 'RT::Ticket-Role' and groups.instance = 388)
)
and
groups.type = acl.principaltype
and
groups.id = principals.id
and
principals.principaltype = 'Group';
This ends up returning 12 rows in only 33msec, a speed improvement of about
200 times! (see rt-union-query attachment)
This part alone is making a huge difference to the peformance of the query;
I'm still looking at joining the other parts to concoct a fast query that
does the same job as the original (as seen in the grandparent to this
message).
The tough part for SearchBuilder is to know when to use UNION and when to
use OR, it's not going to be easy to know the difference. The other option
is to perform each query through SearchBuilder and merge the results in the
RT code, which is a bit of an inelegant solution. Perhaps giving
SearchBuilder hints as to which it should use as part of the function
arguments is an option.
--
jaq at spacepants.org http://spacepants.org/jaq.gpg
-------------- next part --------------
rt3=# select distinct principals.* from acl, principals, groups where (
rt3(# acl.principalid = principals.id and principals.id = groups.id and acl.principaltype = 'Group' and groups.domain in ('SystemInternal', 'UserDefined', 'ACLEquivalence')
rt3(# ) or (
rt3(# ((groups.domain = 'RT::Queue-Role' and groups.instance = 5) or (groups.domain = 'RT::Ticket-Role' and groups.instance = 388)) and groups.type = acl.principaltype and groups.id = principals.id and principals.principaltype = 'Group'
rt3(# );
id | principaltype | objectid | disabled
------+---------------+----------+----------
2 | Group | 2 | 0
3 | Group | 3 | 0
11 | Group | 11 | 0
13 | Group | 13 | 0
31 | Group | 31 | 0
37 | Group | 37 | 0
45 | Group | 45 | 0
47 | Group | 47 | 0
61 | Group | 61 | 0
76 | Group | 76 | 0
87 | Group | 87 | 0
1684 | Group | 1684 | 0
(12 rows)
rt3=# explain analyze
rt3-#
rt3-# select distinct principals.* from acl, principals, groups where (
rt3(# acl.principalid = principals.id and principals.id = groups.id and acl.principaltype = 'Group' and groups.domain in ('SystemInternal', 'UserDefined', 'ACLEquivalence')
rt3(# ) or (
rt3(# ((groups.domain = 'RT::Queue-Role' and groups.instance = 5) or (groups.domain = 'RT::Ticket-Role' and groups.instance = 388)) and groups.type = acl.principaltype and groups.id = principals.id and principals.principaltype = 'Group'
rt3(# );
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=730.04..730.05 rows=1 width=347) (actual time=6490.96..6491.15 rows=12 loops=1)
-> Sort (cost=730.04..730.04 rows=1 width=347) (actual time=6490.95..6490.97 rows=63 loops=1)
Sort Key: principals.id, principals.principaltype, principals.objectid, principals.disabled
-> Nested Loop (cost=0.00..730.03 rows=1 width=347) (actual time=0.19..6490.50 rows=63 loops=1)
Join Filter: ((("inner".principalid = "outer".id) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner".principalid = "outer".id) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principalid = "outer".id) OR ("outer".instance = 388) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principalid = "outer".id) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer".instance = 5)) AND (("inner".principalid = "outer".id) OR ("outer".instance = 388) OR ("outer".instance = 5)) AND (("inner".principalid = "outer".id) OR ("outer"."type" = "inner".principaltype)) AND (("outer".id = "outer".id) OR ("outer"."type" = "inner".principaltype)) AND (("inner".principalid = "outer".id) OR ("outer".id = "outer".id)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".id = "outer".id)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".instance = 388) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer".instance = 5)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".instance = 388) OR ("outer".instance = 5)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer"."type" = "inner".principaltype)) AND (("outer"."domain" = 'SystemInternal'::character varying) OR ("outer"."domain" = 'UserDefined'::character varying) OR ("outer"."domain" = 'ACLEquivalence'::character varying) OR ("outer"."type" = "inner".principaltype)))
-> Nested Loop (cost=0.00..721.28 rows=1 width=288) (actual time=0.13..6305.58 rows=338 loops=1)
Join Filter: ((("inner".id = "outer".id) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".id = "outer".id) OR ("outer".instance = 388) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".id = "outer".id) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer".instance = 5)) AND (("inner".id = "outer".id) OR ("outer".instance = 388) OR ("outer".instance = 5)) AND (("inner".id = "outer".id) OR ("outer".id = "inner".id)) AND (("outer"."domain" = 'SystemInternal'::character varying) OR ("outer"."domain" = 'UserDefined'::character varying) OR ("outer"."domain" = 'ACLEquivalence'::character varying) OR ("outer".id = "inner".id)) AND (("inner".id = "outer".id) OR ("inner".principaltype = 'Group'::character varying)) AND (("outer"."domain" = 'SystemInternal'::character varying) OR ("outer"."domain" = 'UserDefined'::character varying) OR ("outer"."domain" = 'ACLEquivalence'::character varying) OR ("inner".principaltype = 'Group'::character varying)))
-> Seq Scan on groups (cost=0.00..307.80 rows=1 width=236) (actual time=0.07..47.08 rows=338 loops=1)
Filter: ((("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = 388) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR (instance = 5)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = 388) OR (instance = 5)))
-> Seq Scan on principals (cost=0.00..80.56 rows=4756 width=52) (actual time=0.00..8.30 rows=4828 loops=338)
-> Seq Scan on acl (cost=0.00..1.62 rows=62 width=59) (actual time=0.00..0.10 rows=62 loops=338)
Total runtime: 6491.40 msec
(12 rows)
-------------- next part --------------
rt3=# select principals.* from acl, principals, groups where acl.principalid = principals.id and principals.id = groups.id and acl.principaltype = 'Group' and groups.domain in ('SystemInternal', 'UserDefined', 'ACLEquivalence')
rt3-#
rt3-# union
rt3-#
rt3-# select principals.* from groups, acl, principals where ((groups.domain = 'RT::Queue-Role' and groups.instance = 5) or (groups.domain = 'RT::Ticket-Role' and groups.instance = 388)) and groups.type = acl.principaltype and groups.id = principals.id and principals.principaltype = 'Group';
id | principaltype | objectid | disabled
------+---------------+----------+----------
2 | Group | 2 | 0
3 | Group | 3 | 0
11 | Group | 11 | 0
13 | Group | 13 | 0
31 | Group | 31 | 0
37 | Group | 37 | 0
45 | Group | 45 | 0
47 | Group | 47 | 0
61 | Group | 61 | 0
76 | Group | 76 | 0
87 | Group | 87 | 0
1684 | Group | 1684 | 0
(12 rows)
rt3=# explain analyze
rt3-#
rt3-# select principals.* from acl, principals, groups where acl.principalid = principals.id and principals.id = groups.id and acl.principaltype = 'Group' and groups.domain in ('SystemInternal', 'UserDefined', 'ACLEquivalence')
rt3-#
rt3-# union
rt3-#
rt3-# select principals.* from groups, acl, principals where ((groups.domain = 'RT::Queue-Role' and groups.instance = 5) or (groups.domain = 'RT::Ticket-Role' and groups.instance = 388)) and groups.type = acl.principaltype and groups.id = principals.id and principals.principaltype = 'Group';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=167.24..167.27 rows=1 width=225) (actual time=33.07..33.26 rows=12 loops=1)
-> Sort (cost=167.24..167.25 rows=2 width=225) (actual time=33.07..33.09 rows=63 loops=1)
Sort Key: id, principaltype, objectid, disabled
-> Append (cost=0.00..167.23 rows=2 width=225) (actual time=0.12..32.61 rows=63 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..13.73 rows=1 width=60) (actual time=0.11..2.03 rows=61 loops=1)
-> Nested Loop (cost=0.00..13.73 rows=1 width=60) (actual time=0.11..1.92 rows=61 loops=1)
Join Filter: ("outer".principalid = "inner".id)
-> Nested Loop (cost=0.00..7.76 rows=1 width=8) (actual time=0.08..1.26 rows=61 loops=1)
-> Seq Scan on acl (cost=0.00..1.77 rows=1 width=4) (actual time=0.03..0.28 rows=61 loops=1)
Filter: (principaltype = 'Group'::character varying)
-> Index Scan using groups_pkey on groups (cost=0.00..5.97 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=61)
Index Cond: (groups.id = "outer".principalid)
Filter: (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying))
-> Index Scan using principals_pkey on principals (cost=0.00..5.95 rows=1 width=52) (actual time=0.01..0.01 rows=1 loops=61)
Index Cond: (principals.id = "outer".id)
-> Subquery Scan "*SELECT* 2" (cost=145.60..153.50 rows=1 width=225) (actual time=30.47..30.53 rows=2 loops=1)
-> Nested Loop (cost=145.60..153.50 rows=1 width=225) (actual time=30.47..30.52 rows=2 loops=1)
-> Hash Join (cost=145.60..147.53 rows=1 width=173) (actual time=30.43..30.46 rows=2 loops=1)
Hash Cond: ("outer".principaltype = "inner"."type")
-> Seq Scan on acl (cost=0.00..1.62 rows=62 width=55) (actual time=0.01..0.09 rows=62 loops=1)
-> Hash (cost=145.60..145.60 rows=1 width=118) (actual time=30.25..30.25 rows=0 loops=1)
-> Index Scan using groups1, groups1 on
groups (cost=0.00..145.60 rows=1 width=118) (actual time=10.80..30.22 rows=8 loops=1)
Index Cond: (("domain" = 'RT::Ticket-Role'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) Filter: (((instance = 388) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'RT::Ticket-Role'::character varying) OR (instance = 5)) AND ((instance = 388) OR (instance = 5))) -> Index Scan using principals_pkey on principals (cost=0.00..5.96 rows=1 width=52) (actual time=0.02..0.02 rows=1 loops=2)
Index Cond: ("outer".id = principals.id)
Filter: (principaltype = 'Group'::character varying)
Total runtime: 33.80 msec
(28 rows)
More information about the Rt-devel
mailing list