[rt-users] Slower ticket display with RT-IR installed
ktm at rice.edu
ktm at rice.edu
Wed Dec 5 12:31:11 EST 2012
On Wed, Dec 05, 2012 at 07:55:23PM +0400, Ruslan Zakirov wrote:
> On Wed, Dec 5, 2012 at 7:11 PM, ktm at rice.edu <ktm at rice.edu> wrote:
> > Dear RT community,
> >
> > I have been trying to track down some ticket display slowdowns for our
> > upgrade from 3.8.5 to 3.8.13+security patches. One difference in the
> > new version is that we have the latest RT-IR installed. Now there is
> > a noticable pause during the population of the ticket metadata right
> > after the "The Basics" pane. When I enabled query logging in the DB
> > (postgreSQL), I see almost 700 queries of the type:
> >
> > 2012-12-05 08:57:40.365 CSTLOG: duration: 0.325 ms statement: SELECT DISTINCT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id ) WHERE (LOWER(main.Name) = 'constituency') AND (ObjectCustomFields_1.ObjectId = '28') AND (main.LookupType = 'RT::Queue-RT::Ticket') ORDER BY main.Disabled ASC LIMIT 1
> > 2012-12-05 08:57:40.366 CSTLOG: duration: 0.512 ms statement: SELECT DISTINCT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id ) WHERE (LOWER(main.Name) = 'constituency') AND (ObjectCustomFields_1.ObjectId = '0') AND (main.LookupType = 'RT::Queue-RT::Ticket') ORDER BY main.Disabled ASC LIMIT 1
> >
> > That is a .3 seconds total for something that is essentially repeating
> > the same two queries back to back that take a total of 0.85 ms. Is there
> > something that I have misconfigured? Any ideas? I have attached the full
> > set of queries.
>
> 1) File a bug, we should try to avoid running this queries for not RTIR tickets.
> 2) Send explain of the query
> 3) Optimize execution with an index or two, it shouldn't take that
> long to load a CF
>
Hi Ruslan,
I will log a bug report. Here are the EXPLAIN commands for these two queries:
rt3813=# explain analyze SELECT DISTINCT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id ) WHERE (LOWER(main.Name) = 'constituency') AND (ObjectCustomFields_1.ObjectId = '27') AND (main.LookupType = 'RT::Queue-RT::Ticket') ORDER BY main.Disabled ASC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.60..3.64 rows=1 width=99) (actual time=0.051..0.051 rows=0 loops=1)
-> Unique (cost=3.60..3.64 rows=1 width=99) (actual time=0.050..0.050 rows=0 loops=1)
-> Sort (cost=3.60..3.61 rows=1 width=99) (actual time=0.049..0.049 rows=0 loops=1)
Sort Key: main.disabled, main.id, main.name, main.type, main.description, main.sortorder, main.creator, main.created, main.lastupdatedby, main.lastupdated, main.repeated, main.pattern, main.maxvalues
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..3.59 rows=1 width=99) (actual time=0.034..0.034 rows=0 loops=1)
-> Index Scan using objectcustomfields_1 on objectcustomfields objectcustomfields_1 (cost=0.00..1.06 rows=5 width=4) (actual time=0.006..0.008 rows=5 loops=1)
Index Cond: (objectid = 27)
-> Index Scan using customfields_pkey on customfields main (cost=0.00..0.48 rows=1 width=99) (actual time=0.004..0.004 rows=0 loops=5)
Index Cond: (id = objectcustomfields_1.customfield)
Filter: (((lookuptype)::text = 'RT::Queue-RT::Ticket'::text) AND (lower((name)::text) = 'constituency'::text))
Rows Removed by Filter: 1
Total runtime: 0.107 ms
(13 rows)
rt3813=# explain analyze SELECT DISTINCT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id ) WHERE (LOWER(main.Name) = 'constituency') AND (ObjectCustomFields_1.ObjectId = '0') AND (main.LookupType = 'RT::Queue-RT::Ticket') ORDER BY main.Disabled ASC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=13.64..13.68 rows=1 width=99) (actual time=0.274..0.274 rows=0 loops=1)
-> Unique (cost=13.64..13.68 rows=1 width=99) (actual time=0.273..0.273 rows=0 loops=1)
-> Sort (cost=13.64..13.65 rows=1 width=99) (actual time=0.273..0.273 rows=0 loops=1)
Sort Key: main.disabled, main.id, main.name, main.type, main.description, main.sortorder, main.creator, main.created, main.lastupdatedby, main.lastupdated, main.repeated, main.pattern, main.maxvalues
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=10.28..13.63 rows=1 width=99) (actual time=0.265..0.265 rows=0 loops=1)
Hash Cond: (objectcustomfields_1.customfield = main.id)
-> Index Scan using objectcustomfields_1 on objectcustomfields objectcustomfields_1 (cost=0.00..3.16 rows=44 width=4) (actual time=0.005..0.025 rows=43 loops=1)
Index Cond: (objectid = 0)
-> Hash (cost=10.25..10.25 rows=1 width=99) (actual time=0.226..0.226 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on customfields main (cost=0.00..10.25 rows=1 width=99) (actual time=0.197..0.223 rows=2 loops=1)
Filter: (((lookuptype)::text = 'RT::Queue-RT::Ticket'::text) AND (lower((name)::text) = 'constituency'::text))
Rows Removed by Filter: 258
Total runtime: 0.314 ms
(15 rows)
Regards,
Ken
More information about the rt-users
mailing list