RT generating a result with 6,133,699,845,237 rows

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@usit.uio.no
Center for Information Technology Services
University of Oslo, Norway

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

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?

PGP.sig (186 Bytes)

Jesse Vincent wrote:

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?

Hello

I do not have access to a 3.6.6 system now. I will talk to the guys in
charge of the application part of the system and will come back to you
with more information when I have it.

Rafael Martinez, r.m.guerrero@usit.uio.no
Center for Information Technology Services
University of Oslo, Norway

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