Re: Query improvement

On Mon, May 2, 2011 at 10:54 PM, Mark <Marek(dot)Balgar(at)seznam(dot)cz> 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;