Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB.
The database schema is as follows, it's about 2 million rows:
I can query the database on it's own very quickly (<1s) with a query such as:
However, when RT runs a query that's supposed to perform a fulltext index search on the same table, it usually takes hundreds of seconds to complete. The query analyze output is as follows:
Query:
EXPLAIN ANALYZE output:
As far as I can tell, the issue appears to be that it's not using the index created on the contentindex field (contentindex_idx), rather it's doing a filter on a large number of matching rows in the attachments table. The row counts in the explain output also appear to be wildly inaccurate, even after a recent ANALYZE: estimated rows=6507 actual rows=1714818.
I'm not really sure where to go next with this.