Re: Query improvement - Mailing list pgsql-performance

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