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

[Jesse Vincent]

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 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

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=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/
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080520/758272f9/attachment.sig>