Re: Query improvement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux