Re: SeqScan with full text search

2012/4/16 Tomek Walkuski <tomek(dot)walkuski(at)gmail(dot)com>

> Hello group!
>
> I have query like this:
>
> SELECT
> employments.candidate_id AS candidate_id,
> SUM(TS_RANK(employers.search_vector, TO_TSQUERY('simple', 'One:* |
> Two:* | Three:* | Four:*'), 2)) AS ts_rank
> FROM
> employments
> INNER JOIN
> employers ON employments.employer_id = employers.id
> AND
> employers.search_vector @@ TO_TSQUERY('simple', 'One:* | Two:* |
> Three:* | Four:*')
> GROUP BY
> candidate_id;
>
> And it results with this:
>
> http://explain.depesz.com/s/jLM
>
> The JOIN between employments and employers is the culprit. I'm unable
> to get rid of the seq scan, and setting enable_seqscan to off makes
> things even worse.
>
> Is there any way to get rid of this JOIN?
>
>
Have you got an index on employments.employer_id? It seems for me that only
some employments get out of join, so index would help here. What's the plan
with seq_scan off?

P.S. I don't see why all employments are needed. May be I am reading
something wrong? For me it's max 2616 employments out of 1606432.

Best regards, Vitalii Tymchyshyn

--
Best regards,
Vitalii Tymchyshyn