Hi Jeff, Just a quick glance at your plan seems to indicate that there is a fairly wide discrepency between how many results a query returns and what the planner thinks the query should return. Have you already bumped the planner statistics target to 100 or 200 and then re-analyzed the database. I do not think that 8.1 had autovacuum enabled by default so you should enable it to keep the statistics updated and run a full database-wide re-analyze to update the stats. Then try your query again.
Regards, Ken On Wed, Sep 22, 2010 at 12:57:58PM -0400, Jeff Blaine wrote: > We have this in place: > > CREATE INDEX ObjectCustomFieldValues3 ON ObjectCustomFieldValues > (ObjectId,ObjectType); > > And I don't see any other suggested indexes in the wiki. > Maybe I am missing a certain page? Should I add these: > > http://wiki.bestpractical.com/view/DatabaseIndexes > > Here's the EXPLAIN for one of the slow statements: > > rt3=# EXPLAIN ANALYZE SELECT DISTINCT main.id AS id, main.Content AS > content, main.ContentType AS contenttype, main.TransactionId AS > transactionid, main.ContentEncoding AS contentencoding FROM Attachments > main JOIN Transactions Transactions_1 ON ( Transactions_1.id = > main.TransactionId ) JOIN Tickets Tickets_2 ON ( Tickets_2.id = > Transactions_1.ObjectId ) WHERE (Tickets_2.EffectiveId = '35339') AND > (Transactions_1.ObjectType = 'RT::Ticket') AND (main.ContentType = > 'text/plain' OR main.ContentType LIKE 'message/%' OR main.ContentType = > 'text') ORDER BY main.id ASC; > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------ > Unique (cost=1033.85..1033.86 rows=1 width=396) (actual > time=3720.579..3720.579 rows=1 loops=1) > -> Sort (cost=1033.85..1033.85 rows=1 width=396) (actual > time=3720.373..3720.373 rows=1 loops=1) > Sort Key: main.id, main.content, main.contenttype, > main.transactionid, main.contentencoding > -> Nested Loop (cost=17.77..1033.84 rows=1 width=396) (actual > time=3569.107..3719.649 rows=1 loops=1) > -> Nested Loop (cost=14.13..383.55 rows=1 width=4) (actual > time=3562.353..3717.041 rows=28 loops=1) > -> Bitmap Heap Scan on transactions transactions_1 > (cost=14.13..154.48 rows=38 width=8) (actual time=159.630..1185.953 > rows=75440 loops=1) > Recheck Cond: ((objecttype)::text = > 'RT::Ticket'::text) > -> Bitmap Index Scan on transactions1 > (cost=0.00..14.13 rows=38 width=0) (actual time=156.409..156.409 > rows=133079 loops=1) > Index Cond: ((objecttype)::text = > 'RT::Ticket'::text) > -> Index Scan using tickets4 on tickets tickets_2 > (cost=0.00..6.02 rows=1 width=4) (actual time=0.026..0.026 rows=0 > loops=75440) > Index Cond: (tickets_2.id = "outer".objectid) > Filter: (effectiveid = 35339) > -> Bitmap Heap Scan on attachments main (cost=3.63..648.02 > rows=181 width=396) (actual time=0.078..0.082 rows=0 loops=28) > Recheck Cond: ("outer".id = main.transactionid) > Filter: (((contenttype)::text = 'text/plain'::text) OR > ((contenttype)::text ~~ 'message/%'::text) OR ((contenttype)::text = > 'text'::text)) > -> Bitmap Index Scan on attachments2 (cost=0.00..3.63 > rows=181 width=0) (actual time=0.011..0.011 rows=1 loops=28) > Index Cond: ("outer".id = main.transactionid) > Total runtime: 3722.252 ms > (18 rows) > > rt3=# > > > On 9/22/2010 12:24 PM, Kenneth Marshall wrote: >> Hi Jeff, >> >> Wow, is that version of PostgreSQL old. Certainly there have >> been many, many performance improvements since v8.1.x. While >> upgrading would help, you really need to run an EXPLAIN ANALYZE >> for your slow queries to see if there is a problem area. Also, >> you should check to wiki to see if you have all of the recommended >> PostgreSQL indexes. >> >> Some things to check. >> Ken >> >> On Wed, Sep 22, 2010 at 12:12:59PM -0400, Jeff Blaine wrote: >>> Please bear with my ignorance on this topic. I realize >>> this isn't a PostgreSQL tuning forum, but since it's RT-related >>> I thought I'd ask here. >>> >>> RT 3.8.7 (and RTFM) with PostgreSQL 8.1.18 via RHELv5 running >>> as a VM. >>> >>> We're experiencing significant delays today with various >>> PostgreSQL statements taking longer than 1000ms to complete. >>> >>> We have 10,000 tickets, 1300 of which are 'open' >>> >>> Can anyone throw out any ideas, things to check, etc? >>> Is this just >>> >>> Viewing a single ticket results in the following, showing >>> only those longer than 1000ms: >>> >>> LOG: duration: 1496.023 ms >>> LOG: duration: 1496.023 ms statement: EXECUTE<unnamed> [PREPARE: >>> SELECT >>> DISTINCT main.Id AS id, main.Filename AS filename, main.Headers AS >>> headers, >>> main.Subject AS subject, main.Parent AS parent, main.ContentEncoding AS >>> contentencoding, main.ContentType AS contenttype, main.TransactionId AS >>> transactionid, main.Created AS created FROM Attachments main JOIN >>> Transactions Transactions_1 ON ( Transactions_1.id = main.TransactionId >>> ) >>> JOIN Tickets Tickets_2 ON ( Tickets_2.id = Transactions_1.ObjectId ) >>> WHERE (Tickets_2.EffectiveId = '35803') AND (Transactions_1.ObjectType = >>> 'RT::Ticket') ORDER BY main.id ASC ] >>> LOG: duration: 2207.968 ms >>> LOG: duration: 2207.968 ms statement: EXECUTE<unnamed> [PREPARE: >>> SELECT >>> DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN Principals >>> Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers >>> CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = >>> Principals_1.id >>> ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) >>> WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = >>> Groups_3.Type) AND (Principals_1.id != '1') AND >>> (Principals_1.PrincipalType >>> = 'User') AND (ACL_4.RightName = 'OwnTicket' OR ACL_4.RightName = >>> 'SuperUser') AND (Groups_3.Domain = 'RT::Queue-Role' AND >>> Groups_3.Instance >>> = '1') AND ((ACL_4.ObjectType = 'RT::Queue' AND ACL_4.ObjectId = 1) OR >>> (ACL_4.ObjectType = 'RT::System')) ORDER BY main.Name ASC ] >>> LOG: duration: 3363.967 ms >>> LOG: duration: 3363.967 ms statement: EXECUTE<unnamed> [PREPARE: >>> SELECT >>> DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN Principals >>> Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers >>> CachedGroupMembers_3 ON ( CachedGroupMembers_3.MemberId = >>> Principals_1.id >>> ) WHERE (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId = >>> CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND >>> (ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType = 'User') >>> AND (ACL_2.RightName = 'OwnTicket' OR ACL_2.RightName = 'SuperUser') AND >>> ((ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 1) OR >>> (ACL_2.ObjectType = 'RT::System')) ORDER BY main.Name ASC ] >>> LOG: duration: 1482.014 ms >>> LOG: duration: 1482.014 ms statement: EXECUTE<unnamed> [PREPARE: >>> SELECT >>> DISTINCT main.id AS id, main.Content AS content, main.ContentType AS >>> contenttype, main.TransactionId AS transactionid, main.ContentEncoding AS >>> contentencoding FROM Attachments main JOIN Transactions Transactions_1 >>> ON >>> ( Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2 ON ( >>> Tickets_2.id = Transactions_1.ObjectId ) WHERE (Tickets_2.EffectiveId = >>> '35803') AND (Transactions_1.ObjectType = 'RT::Ticket') AND >>> (main.ContentType = 'text/plain' OR main.ContentType LIKE 'message/%' OR >>> main.ContentType = 'text') ORDER BY main.id ASC ] >>> LOG: duration: 1329.789 ms >>> LOG: duration: 1329.789 ms statement: EXECUTE<unnamed> [PREPARE: >>> SELECT >>> DISTINCT main.* FROM Transactions main JOIN Tickets Tickets_1 ON ( >>> Tickets_1.id = main.ObjectId ) WHERE (main.ObjectType = 'RT::Ticket') >>> AND >>> (Tickets_1.EffectiveId = '35803') ORDER BY main.Created ASC, main.id ASC >>> ] >>> >>> >>> # /sbin/sysctl kernel.shmmax kernel.shmall kernel.shmmni kernel.sem >>> kernel.shmmax = 4294967296 >>> kernel.shmall = 2097152 >>> kernel.shmmni = 4096 >>> kernel.sem = 250 32000 100 128 >>> # cat /proc/cpuinfo >>> processor : 0 >>> vendor_id : GenuineIntel >>> cpu family : 6 >>> model : 15 >>> model name : Intel(R) Xeon(R) CPU X5365 @ 3.00GHz >>> stepping : 1 >>> cpu MHz : 2992.499 >>> cache size : 4096 KB >>> fpu : yes >>> fpu_exception : yes >>> cpuid level : 10 >>> wp : yes >>> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge >>> mca >>> cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx lm >>> constant_tsc up pni ssse3 cx16 lahf_lm >>> bogomips : 5984.99 >>> clflush size : 64 >>> cache_alignment : 64 >>> address sizes : 40 bits physical, 48 bits virtual >>> power management: >>> >>> # cat /proc/meminfo >>> MemTotal: 2059588 kB >>> MemFree: 664456 kB >>> Buffers: 21620 kB >>> Cached: 612024 kB >>> SwapCached: 25724 kB >>> Active: 1164032 kB >>> Inactive: 116264 kB >>> HighTotal: 0 kB >>> HighFree: 0 kB >>> LowTotal: 2059588 kB >>> LowFree: 664456 kB >>> SwapTotal: 2064376 kB >>> SwapFree: 1997364 kB >>> Dirty: 400 kB >>> Writeback: 0 kB >>> AnonPages: 644712 kB >>> Mapped: 75512 kB >>> Slab: 78748 kB >>> PageTables: 16492 kB >>> NFS_Unstable: 0 kB >>> Bounce: 0 kB >>> CommitLimit: 3094168 kB >>> Committed_AS: 1383072 kB >>> VmallocTotal: 34359738367 kB >>> VmallocUsed: 263920 kB >>> VmallocChunk: 34359473927 kB >>> HugePages_Total: 0 >>> HugePages_Free: 0 >>> HugePages_Rsvd: 0 >>> Hugepagesize: 2048 kB >>> # >>> >>> RT Training in Washington DC, USA on Oct 25& 26 2010 >>> Last one this year -- Learn how to get the most out of RT! >>> >> > RT Training in Washington DC, USA on Oct 25 & 26 2010 Last one this year -- Learn how to get the most out of RT!
