Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
    
Can you provide explain plan? Put explain plan here: explain.depesz.com – Mladen Uzelac Dec 19 '14 at 16:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.