On May 20, 2008, at 4:41 PM, Rafael Martinez wrote:
Hello We have a type of SQL statement that sometimes runs in our RT databasewhich consumes a lot resources and never ends (because we kill it). Thereason of this is that in the process of getting the result via some joins it tries to generate a result with 6,133,699,845,237 rows. Any suggestions about how we can fix this?. Should I contact the postgresql guys? System info: RT 3.6.1 with postgresql.8.2.6
Can you test with RT 3.6.6?
Query plan information: --------------------------------------------------------------- rtprod=# EXPLAIN SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId =main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( ( (main.Owner = '18651') ) or (main.LastUpdatedBy = '18651') )AND ( ( (Attachments_2.Content ILIKE '%kloning%') AND (Attachments_2.TransactionId = Transactions_1.id) AND (main.id = Transactions_1.ObjectId) ) OR (main.Subject ILIKE '%kopi%') ) ) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2544784714227.59..2544784714227.60 rows=1 width=4) -> Nested Loop (cost=127120.96..2544768019893.95 rows=6677733454 width=4) Join Filter: (((attachments_2.content ~~* '%kloning%'::text) AND (attachments_2.transactionid = transactions_1.id) AND (main.id =transactions_1.objectid)) OR ((main.subject)::text ~~* '%kopi %'::text))-> Nested Loop (cost=114554.67..152625067690.14 rows=6133699845237 width=371) -> Seq Scan on attachments attachments_2 (cost=0.00..455450.73 rows=2757873 width=363)-> Materialize (cost=114554.67..147655.36 rows=2224069width=8) -> Seq Scan on transactions transactions_1 (cost=0.00..101470.60 rows=2224069 width=8) Filter: ((objecttype)::text = 'RT::Ticket'::text) -> Materialize (cost=12566.29..12566.42 rows=13 width=38) -> Seq Scan on tickets main (cost=0.00..12566.28 rows=13 width=38)Filter: ((effectiveid = id) AND ((status)::text <>'deleted'::text) AND (("type")::text = 'ticket'::text) AND (("owner" = 18651) OR (lastupdatedby = 18651))) (11 rows) --------------------------------------------------------------- -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
PGP.sig
Description: This is a digitally signed message part
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
