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

'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