On Mon, May 2, 2011 at 10:54 PM, Mark <Marek.Balgar@xxxxxxxxx> wrote: > but the result have been worst than before. By the way is there a posibility > to create beeter query with same effect? > I have tried more queries, but this has got best performance yet. Well, this seems to be the worst part: (SELECT page_id FROM mediawiki.page WHERE page_id IN (SELECT page_id FROM mediawiki.page WHERE (titlevector @@ (to_tsquery('fotbal')))) OR page_id IN (SELECT p.page_id from mediawiki.page p,mediawiki.revision r, (SELECT old_id FROM mediawiki.pagecontent WHERE (textvector @@ (to_tsquery('fotbal')))) ss WHERE (p.page_id=r.rev_page AND r.rev_id=ss.old_id))) If you're running a new enough pg (8.4+), you could try using CTEs for that. I haven't used CTEs much, but I think it goes something like: WITH someids AS ( (SELECT page_id FROM mediawiki.page WHERE page_id IN (SELECT page_id FROM mediawiki.page WHERE (titlevector @@ (to_tsquery('fotbal')))) OR page_id IN (SELECT p.page_id from mediawiki.page p,mediawiki.revision r, (SELECT old_id FROM mediawiki.pagecontent WHERE (textvector @@ (to_tsquery('fotbal')))) ss WHERE (p.page_id=r.rev_page AND r.rev_id=ss.old_id))) ) SELECT pa.page_id, pa.page_title, ts_rank(pc.textvector,(to_tsquery('fotbal')))+ts_rank(pa.titlevector,(to_tsquery('fotbal')))*10 as totalrank from mediawiki.page pa, mediawiki.revision re, mediawiki.pagecontent pc WHERE pa.page_id in someids AND (pa.page_id=re.rev_page AND re.rev_id=pc.old_id) ORDER BY totalrank LIMIT 100; -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance