On Wed, Dec 05, 2012 at 07:55:23PM +0400, Ruslan Zakirov wrote: > On Wed, Dec 5, 2012 at 7:11 PM, k...@rice.edu <k...@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 -------- We're hiring! http://bestpractical.com/jobs