Hi all,
we have about 18000 Tickets in our RT and the fulltext search is nearly
unusable. One search takes about 210 Seconds.
We use rt3.6.4 with postgresql.
At the postgres logfile I saw that this query is called twice for the ticket
count and (nearly the same) for the output (it takes every time 60 Seconds):
2007-08-29 02:24:52 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id) FROM
Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId =
main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId =
Transactions_1.id ) WHERE (Transactions_1.ObjectType = ‘RT::Ticket’) AND
(main.Status != ‘deleted’) AND ( ( ( Attachments_2.Content
ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’) AND (main.EffectiveId =
main.id)
2007-08-29 02:26:09 CEST LOG: Dauer: 76995.042 ms
2007-08-29 02:26:09 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id) FROM
Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId =
main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId =
Transactions_1.id ) WHERE (Transactions_1.ObjectType = ‘RT::Ticket’) AND
(main.Status != ‘deleted’) AND ( ( ( Attachments_2.Content
ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’) AND (main.EffectiveId =
main.id)
2007-08-29 02:27:15 CEST LOG: Dauer: 65809.656 ms
2007-08-29 02:27:15 CEST LOG: Anweisung: SELECT DISTINCT main.* FROM Tickets
main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId =
main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId =
Transactions_1.id ) WHERE (Transactions_1.ObjectType = ‘RT::Ticket’) AND
(main.Status != ‘deleted’) AND (
( ( Attachments_2.Content ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’)
AND (main.EffectiveId = main.id) ORDER BY main.id ASC LIMIT 50
2007-08-29 02:28:18 CEST LOG: Dauer: 62832.487 ms
Is it possible to merge these 3 queries into one?
This should speed up the fulltext search about 60%
Exists there any patch for this behaviour?
regards
Sven
Quoting sk@net-lab.net:
Hi all,
we have about 18000 Tickets in our RT and the fulltext search is nearly
unusable. One search takes about 210 Seconds.
We use rt3.6.4 with postgresql.
At the postgres logfile I saw that this query is called twice for the ticket
count and (nearly the same) for the output (it takes every time 60 Seconds):
2007-08-29 02:24:52 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id) FROM
Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId =
main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId =
Transactions_1.id ) WHERE (Transactions_1.ObjectType = ‘RT::Ticket’) AND
(main.Status != ‘deleted’) AND ( ( ( Attachments_2.Content
ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’) AND (main.EffectiveId =
main.id)
2007-08-29 02:26:09 CEST LOG: Dauer: 76995.042 ms
2007-08-29 02:26:09 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id) FROM
Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId =
main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId =
Transactions_1.id ) WHERE (Transactions_1.ObjectType = ‘RT::Ticket’) AND
(main.Status != ‘deleted’) AND ( ( ( Attachments_2.Content
ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’) AND (main.EffectiveId =
main.id)
2007-08-29 02:27:15 CEST LOG: Dauer: 65809.656 ms
2007-08-29 02:27:15 CEST LOG: Anweisung: SELECT DISTINCT main.* FROM Tickets
main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId =
main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId =
Transactions_1.id ) WHERE (Transactions_1.ObjectType = ‘RT::Ticket’) AND
(main.Status != ‘deleted’) AND (
( ( Attachments_2.Content ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’)
AND (main.EffectiveId = main.id) ORDER BY main.id ASC LIMIT 50
2007-08-29 02:28:18 CEST LOG: Dauer: 62832.487 ms
Is it possible to merge these 3 queries into one?
This should speed up the fulltext search about 60%
Exists there any patch for this behaviour?
regards
Sven
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Sven,
A fulltext search performs a sequential scan of your entire database. You may
wish to modify the Oracle Text modifications to support full text indexing with
PostgreSQL. You will need to install the tsearch2 contrib module. I will be
doing this in our upcoming move to the 3.6 version of RT and will update the
wiki to cover the changes needed. If you need it now, then you will need to
roll your own. Look on the wiki for the Oracle full text support posted by
Joop. Good luck.
Ken
Quoting sk@net-lab.net:
Hi all,
At the postgres logfile I saw that this query is called twice for the
ticket count and (nearly the same) for the output (it takes every time 60
Seconds):
2007-08-29 02:24:52 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id)
FROM Tickets main JOIN Transactions Transactions_1 ON (
Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON (
Attachments_2.TransactionId = Transactions_1.id ) WHERE
(Transactions_1.ObjectType = ‘RT::Ticket’) AND (main.Status != ‘deleted’)
AND ( ( ( Attachments_2.Content
ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’) AND (main.EffectiveId =
main.id)
2007-08-29 02:26:09 CEST LOG: Dauer: 76995.042 ms
2007-08-29 02:26:09 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id)
FROM Tickets main JOIN Transactions Transactions_1 ON (
Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON (
Attachments_2.TransactionId = Transactions_1.id ) WHERE
(Transactions_1.ObjectType = ‘RT::Ticket’) AND (main.Status != ‘deleted’)
AND ( ( ( Attachments_2.Content
ILIKE ‘%test%’ ) ) ) AND (main.Type = ‘ticket’) AND (main.EffectiveId =
main.id)
2007-08-29 02:27:15 CEST LOG: Dauer: 65809.656 ms
2007-08-29 02:27:15 CEST LOG: Anweisung: SELECT DISTINCT main.* FROM
Tickets main JOIN Transactions Transactions_1 ON (
Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON (
Attachments_2.TransactionId = Transactions_1.id ) WHERE
(Transactions_1.ObjectType = ‘RT::Ticket’) AND (main.Status != ‘deleted’)
AND (
( ( Attachments_2.Content ILIKE ‘%test%’ ) ) ) AND (main.Type =
‘ticket’) AND (main.EffectiveId = main.id) ORDER BY main.id ASC LIMIT
50 2007-08-29 02:28:18 CEST LOG: Dauer: 62832.487 ms
Is it possible to merge these 3 queries into one?
This should speed up the fulltext search about 60%
Exists there any patch for this behaviour?
regards
Sven
Sven,
A fulltext search performs a sequential scan of your entire database. You
may wish to modify the Oracle Text modifications to support full text
indexing with PostgreSQL. You will need to install the tsearch2 contrib
module. I will be doing this in our upcoming move to the 3.6 version of RT
and will update the wiki to cover the changes needed. If you need it now,
then you will need to roll your own. Look on the wiki for the Oracle full
text support posted by Joop. Good luck.
Ken
Hi Ken,
I will try tsearch2 (I hope I get it working). But I don’t understand why the
query is called 2 times. I thought this should be improved.
Sven