From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: SeqScan with full text search
Date: Mon, 16 Apr 2012 18:19:16 +0200
Message-ID: <4F8C4684.5040609@fuzzy.cz> (raw)
In-Reply-To: <5afd048c-19e2-4a81-8439-bc09839d29dc@n5g2000vbf.googlegroups.com>
References: <5afd048c-19e2-4a81-8439-bc09839d29dc@n5g2000vbf.googlegroups.com>
On 16.4.2012 16:02, Tomek Walkuski wrote:
> 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?
Well, it's clearly the seqscan that takes most time, and it seems that
you really need to scan the whole table because you're asking 'for each
employment of all the candidates ...'
So you really need to scan all 1.6 million rows to get the result. And
seqscan is the best way to do that.
I don't see a way to remove the join and/or seqscan, unless you want to
keep a 'materialized view' maintained by a trigger or something ...
Another option is to make the employment table as small as possible
(e.g. removing columns that are not needed etc.) so that the seqscan is
faster.
>
> What info should I post to debug this easier?
1) structures of the tables
2) what amount of data are we talking about
3) was this the first run (with cold caches) or have you run that
several times?
4) basic system info (RAM, CPU, shared_buffers etc.)
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: SeqScan with full text search
In-Reply-To: <4F8C4684.5040609@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox