Re: Query improvement

> 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)))
>


'OR' statements often generate complicated plans. You should try to
rewrite your Query with a n UNION clause.
Using explicit joins may also help the planner:

SELECT page_id
FROM mediawiki.page
WHERE (titlevector @@ (to_tsquery('fotbal')))

UNION

SELECT p.page_id
FROM mediawiki.page p
  JOIN mediawiki.revision r on (p.page_id=r.rev_page)
  JOIN mediawiki.pagecontent ss on (r.rev_id=ss.old_id)
WHERE (ss.textvector @@ (to_tsquery('fotbal')))

HTH,

Marc Mamin