[rt-users] RT generating a result with 6,133,699,845,237 rows

[Rafael Martinez]

Hello

We have a type of SQL statement that sometimes runs in our RT database 
which consumes a lot resources and never ends (because we kill it). The 
reason 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

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=2224069 
width=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, <r.m.guerrero at usit.uio.no>
  Center for Information Technology Services
  University of Oslo, Norway

  PGP Public Key: http://folk.uio.no/rafael/