Your message dated Tue, 16 Jun 2009 22:18:51 +0000
with message-id <[email protected]>
and subject line Bug#512653: fixed in request-tracker3.8 3.8.4-1
has caused the Debian Bug report #512653,
regarding request-tracker3.8: Database schema missing indexes (Postgres)
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact [email protected]
immediately.)
--
512653: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=512653
Debian Bug Tracking System
Contact [email protected] with problems
--- Begin Message ---
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 nic...@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
--- 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
--- End Message ---
--- Begin Message ---
Source: request-tracker3.8
Source-Version: 3.8.4-1
We believe that the bug you reported is fixed in the latest version of
request-tracker3.8, which is due to be installed in the Debian FTP archive:
request-tracker3.8_3.8.4-1.diff.gz
to pool/main/r/request-tracker3.8/request-tracker3.8_3.8.4-1.diff.gz
request-tracker3.8_3.8.4-1.dsc
to pool/main/r/request-tracker3.8/request-tracker3.8_3.8.4-1.dsc
request-tracker3.8_3.8.4-1_all.deb
to pool/main/r/request-tracker3.8/request-tracker3.8_3.8.4-1_all.deb
request-tracker3.8_3.8.4.orig.tar.gz
to pool/main/r/request-tracker3.8/request-tracker3.8_3.8.4.orig.tar.gz
rt3.8-apache2_3.8.4-1_all.deb
to pool/main/r/request-tracker3.8/rt3.8-apache2_3.8.4-1_all.deb
rt3.8-clients_3.8.4-1_all.deb
to pool/main/r/request-tracker3.8/rt3.8-clients_3.8.4-1_all.deb
rt3.8-db-mysql_3.8.4-1_all.deb
to pool/main/r/request-tracker3.8/rt3.8-db-mysql_3.8.4-1_all.deb
rt3.8-db-postgresql_3.8.4-1_all.deb
to pool/main/r/request-tracker3.8/rt3.8-db-postgresql_3.8.4-1_all.deb
rt3.8-db-sqlite_3.8.4-1_all.deb
to pool/main/r/request-tracker3.8/rt3.8-db-sqlite_3.8.4-1_all.deb
A summary of the changes between this version and the previous one is
attached.
Thank you for reporting the bug, which will now be closed. If you
have further comments please address them to [email protected],
and the maintainer will reopen the bug report if appropriate.
Debian distribution maintenance software
pp.
Dominic Hargreaves <[email protected]> (supplier of updated request-tracker3.8
package)
(This message was generated automatically at their request; if you
believe that there is a problem with it please contact the archive
administrators by mailing [email protected])
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Format: 1.8
Date: Tue, 16 Jun 2009 21:46:59 +0100
Source: request-tracker3.8
Binary: request-tracker3.8 rt3.8-clients rt3.8-apache2 rt3.8-db-postgresql
rt3.8-db-mysql rt3.8-db-sqlite
Architecture: source all
Version: 3.8.4-1
Distribution: unstable
Urgency: low
Maintainer: Debian Request Tracker Group
<[email protected]>
Changed-By: Dominic Hargreaves <[email protected]>
Description:
request-tracker3.8 - extensible trouble-ticket tracking system
rt3.8-apache2 - Apache 2 specific files for request-tracker3.8
rt3.8-clients - mail gateway and command-line interface to request-tracker3.8
rt3.8-db-mysql - MySQL database backend for request-tracker3.8
rt3.8-db-postgresql - PostgreSQL database backend for request-tracker3.8
rt3.8-db-sqlite - SQLite database backend for request-tracker3.8
Closes: 238345 512653 512750 518692 519885 519922 520603 520759 520958 520959
521199 521926 522367 522896 523090 523481 524256 524256 524449 524715 524894
525171 525281 526914 533069
Changes:
request-tracker3.8 (3.8.4-1) unstable; urgency=low
.
[ Dominic Hargreaves ]
* Add missing comma in Depends (fixes FTBFS on etch)
* Update debconf translations: pt.po, ja.po, sv.po, it.po, cs.po, ru.po
(Closes: #519885, #519922, #520603, #520759, #521199, #521926)
* Document preference for not using SQLite in production
(Closes: #512750)
.
[ Christian Perrier ]
* Debconf templates and debian/control reviewed by the debian-l10n-
english team as part of the Smith review project.
(Closes: #522367, #520959)
* [Debconf translation updates]
- Japanese. Closes: #522896
- German. Closes: #520958
- Portuguese. Closes: #523481
- Galician. Closes: #524256
- Galician. Closes: #524256
- Spanish. Closes: #524449
- Italian. Closes: #524715
- Russian. Closes: #524894
- Swedish. Closes: #525171
- French. Closes: #525281
.
[ Dominic Hargreaves ]
* Don't tell dbconfig to comment out unused variables, since this
breaks MySQL and Postgres database configuration (Closes: #523090)
* Update Standards-Version (no changes)
* Switch dependency on sysklogd to rsyslog (Closes: #526914)
* New upstream release; includes
- Minor security fix (Closes: #533069)
- Add missing Postgres index (Closes: #512653)
* Patch webmux.pl to provide a better error message when the wrong
major version of RT is in @INC (for example in a mod_perl context).
(Closes: #518692)
* Add some more example Exim 4 configuration (Closes: #238345)
* Don't apply database ACLs in databases managed by dbconfig-common.
* Remove unused ACL patch
Checksums-Sha1:
596dfb9a2abb46a3f633def1f404a7b7b822e82c 1635 request-tracker3.8_3.8.4-1.dsc
c786eb78dd6c8374da3bc0dd10414e040d69864f 3320207
request-tracker3.8_3.8.4.orig.tar.gz
edd4915f718ccc7cff855038e814e79eb0305869 59591
request-tracker3.8_3.8.4-1.diff.gz
be6161afcadc08388be0e2e9a9f0469be17e976a 2929460
request-tracker3.8_3.8.4-1_all.deb
2215c41432e67c2970fba654fea9c9ce6a1f47a9 43830 rt3.8-clients_3.8.4-1_all.deb
aa7a497732bef232a4df863c9d30051dfa750ac8 8868 rt3.8-apache2_3.8.4-1_all.deb
adf27b3b37b6b2d1f999285e0f19075651719302 7604
rt3.8-db-postgresql_3.8.4-1_all.deb
372836c24e6224aaa390187da9c2548a17db82e0 7604 rt3.8-db-mysql_3.8.4-1_all.deb
3ef00c0accd2c3a90e024d15a3b7768384d2693a 7702 rt3.8-db-sqlite_3.8.4-1_all.deb
Checksums-Sha256:
7794290f43cbee0261c8a3b9d5f9057dae22c91398c4f810883d6ce5bd34723c 1635
request-tracker3.8_3.8.4-1.dsc
7fcbc56755e1f732c5dfaa59da921749820f0ca6deae7b4bde5c20acde9541ee 3320207
request-tracker3.8_3.8.4.orig.tar.gz
187dcd47024d86335eeea8a38687d150675727decc1cb51c98284bb850f39c12 59591
request-tracker3.8_3.8.4-1.diff.gz
15e966f1b40c9af9463c45cdfdda40614142e7d498d82c59cbd537c463367272 2929460
request-tracker3.8_3.8.4-1_all.deb
6c782f81509d83867f8396a6fc73fbdbaa754130032243f5dfa630adb247094e 43830
rt3.8-clients_3.8.4-1_all.deb
196e2c9fe019662fcb64c779144055e30fa26e45a072bfd7d640f0b0dfa5995d 8868
rt3.8-apache2_3.8.4-1_all.deb
36fe6746e248bada09b5330bf7ef5d8e570d50bc31666e2a25b58c417bd156f0 7604
rt3.8-db-postgresql_3.8.4-1_all.deb
d10fae3412b1f921403ebf5361223f5cd1f6d501d856d216246e76d11c243690 7604
rt3.8-db-mysql_3.8.4-1_all.deb
fc60b0e7193fb213744a4d6ba6c95fefc4aedf2b54bfccadd67fd0a8d695e775 7702
rt3.8-db-sqlite_3.8.4-1_all.deb
Files:
cacb573bb37aaaeadbdae3090dd177fd 1635 misc optional
request-tracker3.8_3.8.4-1.dsc
cdfdbd954203a8bc2c663a6847218ccc 3320207 misc optional
request-tracker3.8_3.8.4.orig.tar.gz
d8f9582e7b3fd09baceef47f414af881 59591 misc optional
request-tracker3.8_3.8.4-1.diff.gz
4e1dd723b4e467396b0ebf31677a3c60 2929460 misc optional
request-tracker3.8_3.8.4-1_all.deb
30ef38535f18192bff16d77f85edf6f2 43830 misc optional
rt3.8-clients_3.8.4-1_all.deb
9b34e3c2edd92914550eaa97098a6c38 8868 misc optional
rt3.8-apache2_3.8.4-1_all.deb
d67b5ce85a67b9e024ddca2bb3545b15 7604 misc optional
rt3.8-db-postgresql_3.8.4-1_all.deb
5799a4d185f0b256a4d0ba91e404ed40 7604 misc optional
rt3.8-db-mysql_3.8.4-1_all.deb
f969fdcc3efc9d995fca891303e35f14 7702 misc optional
rt3.8-db-sqlite_3.8.4-1_all.deb
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
iD8DBQFKOBVnYzuFKFF44qURAh6vAJ9lCiHY4VRQDmbD7bCYKvaR+SHwawCeJ1Kc
RXHyHQlHhv+TL8WFR1SdD1c=
=mdCi
-----END PGP SIGNATURE-----
--- End Message ---
