[request-tracker-maintainers] Bug#512653: request-tracker3.8: Database schema missing indexes (Postgres)
Andrew Robert Nicols
andrew.nicols at luns.net.uk
Thu Jan 22 16:03:59 UTC 2009
Package: request-tracker3.8
Version: 3.8.2-1~experimental1
Severity: important
Tags: patch
It looks like the schema files for Postgres are missing at least one INDEXES.
This is also missing in the upstream schema.Pg files on bestpractical's
website.
I've given a brief description and postgres explain output from before and
after when querying on a database with 50,000 tickets.
Index 'GroupMembers1':
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
--
Without this INDEX, ticket loading pauses at every block ( Basics, People, Reminders, Dates, Links, etc) for ACL checks.
The query which seems to hold it up is:
SELECT main.* FROM ( SELECT main.id FROM GroupMembers main JOIN Groups Groups_1 ON ( Groups_1.id = main.GroupId )
WHERE (Groups_1.Domain = 'SystemInternal' OR Groups_1.Domain = 'UserDefined') AND (main.MemberId = '100027') GROUP
BY main.id ORDER BY MIN(Groups_1.Domain) ASC, MIN(Groups_1.Name) ASC ) distinctquery, GroupMembers main WHERE
(main.id = distinctquery.id);
This index (or a very similar one) is present in the files:
587 nicols at banoffee:~/tmp/rt/indexes> grep " GroupMember" index.*
index.Informix:CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
index.mysql:CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
index.mysql-4.1:CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
index.Oracle:CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
index.Sybase:CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
rt=# EXPLAIN ANALYZE SELECT main.* FROM ( SELECT main.id FROM GroupMembers main JOIN Groups Groups_1 ON ( Groups_1.id = main.GroupId ) WHERE (Groups_1.Domain = 'SystemInternal' OR Groups_1.Domain = 'UserDefined') AND (main.MemberId = '100027') GROUP BY main.id ORDER BY MIN(Groups_1.Domain) ASC, MIN(Groups_1.Name) ASC ) distinctquery, GroupMembers main WHERE (main.id = distinctquery.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2058.22..2066.53 rows=1 width=12) (actual time=987.086..987.101 rows=2 loops=1)
-> Sort (cost=2058.22..2058.23 rows=1 width=30) (actual time=987.057..987.059 rows=2 loops=1)
Sort Key: (min((groups_1.domain)::text)), (min((groups_1.name)::text))
Sort Method: quicksort Memory: 17kB
-> HashAggregate (cost=2058.20..2058.21 rows=1 width=30) (actual time=987.034..987.036 rows=2 loops=1)
-> Nested Loop (cost=8.62..2058.19 rows=1 width=30) (actual time=14.805..986.999 rows=2 loops=1)
Join Filter: (main.groupid = groups_1.id)
-> Bitmap Heap Scan on groups groups_1 (cost=8.62..12.64 rows=1 width=30) (actual time=0.057..0.190 rows=33 loops=1)
Recheck Cond: (((domain)::text = 'SystemInternal'::text) OR ((domain)::text = 'UserDefined'::text))
-> BitmapOr (cost=8.62..8.62 rows=1 width=0) (actual time=0.046..0.046 rows=0 loops=1)
-> Bitmap Index Scan on groups1 (cost=0.00..4.31 rows=1 width=0) (actual time=0.020..0.020 rows=3 loops=1)
Index Cond: ((domain)::text = 'SystemInternal'::text)
-> Bitmap Index Scan on groups1 (cost=0.00..4.31 rows=1 width=0) (actual time=0.024..0.024 rows=30 loops=1)
Index Cond: ((domain)::text = 'UserDefined'::text)
-> Seq Scan on groupmembers main (cost=0.00..2044.95 rows=48 width=8) (actual time=12.669..29.420 rows=804 loops=33)
Filter: (main.memberid = 100027)
-> Index Scan using groupmembers_pkey on groupmembers main (cost=0.00..8.28 rows=1 width=12) (actual time=0.014..0.016 rows=1 loops=2)
Index Cond: (main.id = main.id)
Total runtime: 987.239 ms
(19 rows)
rt=# CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
CREATE INDEX
rt=# EXPLAIN ANALYZE SELECT main.* FROM ( SELECT main.id FROM GroupMembers main JOIN Groups Groups_1 ON ( Groups_1.id = main.GroupId ) WHERE (Groups_1.Domain = 'SystemInternal' OR Groups_1.Domain = 'UserDefined') AND (main.MemberId = '100027') GROUP BY main.id ORDER BY MIN(Groups_1.Domain) ASC, MIN(Groups_1.Name) ASC ) distinctquery, GroupMembers main WHERE (main.id = distinctquery.id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=20.97..29.27 rows=1 width=12) (actual time=0.676..0.689 rows=2 loops=1)
-> Sort (cost=20.97..20.97 rows=1 width=30) (actual time=0.662..0.664 rows=2 loops=1)
Sort Key: (min((groups_1.domain)::text)), (min((groups_1.name)::text))
Sort Method: quicksort Memory: 17kB
-> HashAggregate (cost=20.94..20.96 rows=1 width=30) (actual time=0.646..0.648 rows=2 loops=1)
-> Nested Loop (cost=8.62..20.94 rows=1 width=30) (actual time=0.135..0.629 rows=2 loops=1)
-> Bitmap Heap Scan on groups groups_1 (cost=8.62..12.64 rows=1 width=30) (actual time=0.058..0.129 rows=33 loops=1)
Recheck Cond: (((domain)::text = 'SystemInternal'::text) OR ((domain)::text = 'UserDefined'::text))
-> BitmapOr (cost=8.62..8.62 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1)
-> Bitmap Index Scan on groups1 (cost=0.00..4.31 rows=1 width=0) (actual time=0.021..0.021 rows=3 loops=1)
Index Cond: ((domain)::text = 'SystemInternal'::text)
-> Bitmap Index Scan on groups1 (cost=0.00..4.31 rows=1 width=0) (actual time=0.023..0.023 rows=30 loops=1)
Index Cond: ((domain)::text = 'UserDefined'::text)
-> Index Scan using groupmembers1 on groupmembers main (cost=0.00..8.29 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=33)
Index Cond: ((main.groupid = groups_1.id) AND (main.memberid = 100027))
-> Index Scan using groupmembers_pkey on groupmembers main (cost=0.00..8.28 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=2)
Index Cond: (main.id = main.id)
Total runtime: 0.797 ms
(18 rows)
-- System Information:
Debian Release: 5.0
APT prefers testing
APT policy: (500, 'testing')
Architecture: i386 (i686)
Kernel: Linux 2.6.26-1-vserver-686-bigmem (SMP w/2 CPU cores)
Locale: LANG=en_GB.UTF-8, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/bash
-------------- next part --------------
--- schema.Pg 2009-01-22 15:05:25.000000000 +0000
+++ schema.new.Pg 2009-01-22 16:00:25.000000000 +0000
@@ -285,6 +285,8 @@
-- }}}
+CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
+
-- {{{ GroupMembersCache
More information about the pkg-request-tracker-maintainers
mailing list