Running on a large database (around 1.5G) The following query takes about 70 seconds to run,
SELECT x
FROM DBText x
WHERE x.status = 2
AND x.publishedTime < 4574590406397
AND x.service = 'twitter'
AND (NOT EXISTS
(SELECT 1
FROM DBTag t,
DBEquiv oeqv
WHERE (x.oxid=t.txid
OR (x.oxid=oeqv.id1
AND t.txid=oeqv.id2))
AND ((t.tag='ignore-person'
AND t.grp='socrash')
OR (t.tag='lost'
AND t.grp='!cross-project'))))
AND (NOT EXISTS
(SELECT 1
FROM DBTag t
WHERE t.txid=x.xid
AND ((t.tag='ignore'
AND t.grp='socrash'))))
AND (x.group_id = 347804
OR x.group_id IS NULL)
AND (EXISTS
(SELECT 1
FROM DBTag wst
WHERE wst.txid=x.xid
AND wst.grp='socrash'))
AND length(x.contents)<100000
AND to_tsvector('english',coalesce(x.name,'')||' '||coalesce(x.contents,'')) @@ (to_tsquery ('english','apple*') && to_tsquery('english','pie*'))
ORDER BY x.publishedTime DESC LIMIT 10;
However, if the to_tsvector area is removed, or the ORDER BY statement is removed the search becomes a almost instant. I have tried this using LIKE rather than to_tsvector to search the contents and once again, almost instant.
Is there any way to optimize this search so that it still uses ts_vector text searching, but works at a similar speed to the LIKE search?
Thanks