4

I am running PostgreSQL 9.6. These are the relevant definitions:

CREATE TABLE IF NOT EXISTS instagram.profiles_1000 (
    id                          SERIAL PRIMARY KEY,
    username                    VARCHAR(255) NOT NULL UNIQUE,
    followers                   BIGINT,
    tsv                         TSVECTOR
);

CREATE UNIQUE INDEX IF NOT EXISTS instagram_username_index
    ON instagram.profiles_1000(username);
CREATE INDEX IF NOT EXISTS instagram_followers_index
    ON instagram.profiles_1000(followers);
CREATE INDEX IF NOT EXISTS instagram_textsearch_index
    ON instagram.profiles_1000 USING GIN(tsv);

And the text vector is updated by a trigger:

CREATE FUNCTION instagram_documents_search_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
        setweight(to_tsvector(COALESCE(new.username, '')), 'D') || ' ' ||
        setweight(to_tsvector(COALESCE(new.full_name, '')), 'C') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_country, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_region, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.biography, '')), 'A') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_city, '')), 'A');
  return new;
end
$$ LANGUAGE plpgsql;


CREATE TRIGGER instagram_tsvectorupdate BEFORE INSERT OR UPDATE
    ON instagram.profiles_1000 FOR EACH ROW
    EXECUTE PROCEDURE instagram_documents_search_trigger();

This is the query:

select instagram.profiles_1000.*, categories, followers as rank                                                                                            
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv
left outer join instagram.profile_categories_agg on instagram.profiles_1000.username = instagram.profile_categories_agg.username
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

This is the output of EXPLAIN (ANALYZE, BUFFERS):

https://explain.depesz.com/s/ceCd

The culprit is the Bitmap Heap Scan which makes up the bulk of the total execution time. Frankly I don't understand why it's needed, especially since the Bitmap Index Scan on instagram_textsearch_index already filters the rows according to the search term.

Can someone shed some light?

EDIT It was pointed out that I misread the explain output. Indeed, the left outer join was taking a lot of time. I tried to remove it as follows:

select instagram.profiles_1000.*, followers as rank
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv                                              
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

But the query still takes 13 seconds! This is the EXPLAIN (ANALYZE, BUFFERS) output:

https://explain.depesz.com/s/awfH

Now the bottleneck seems to be the full-text search. Is it really that slow? The table has just 5 million rows, and the tsv (which has type TSVECTOR) is indexed by the following index:

CREATE INDEX IF NOT EXISTS instagram_textsearch_index_1000
    ON instagram.profiles_1000 USING GIN(tsv);

EDIT 2 I realized that I can write a leaner query if I only process the profiles that match the search (which are always 50 at most). Using this query:

select p.*, categories
from
    (select id
    from instagram.profiles_1000, plainto_tsquery('arts') as q
    where q @@ tsv and followers is not null and followers > 0
    order by (followers, -id) desc
    limit 50) as ids
inner join instagram.profiles_1000 as p on
    p.id = ids.id
left outer join instagram.profile_categories_agg as c on
    c.username = p.username;

I am able to obtain this result: https://explain.depesz.com/s/OvG

Which puts the search at ~3 seconds. It would be nicer to reach 1 second at least though.

1
  • The identical bitmap heap scan takes anywhere from 1.4 seconds to 13 seconds in your examples, with no clear reason as to why that might be. What this suggests to me is that you have too little RAM on the machine, so useful data is sometimes getting forced out of the cache and so needs to be read back from disk (slowly) and sometimes doesn't.
    – jjanes
    Sep 11 '18 at 18:54
1

If you want improve further on the timing, your best bet might be to abandon use of the FTS index, at least for cases where the @@ match criteria returns a lot of results.

First you would have to change your ORDER BY from order by (followers, -id) desc to order by followers desc, id. This version is semantically equivalent (except perhaps in how it handles NULL values) but it does not go through the step of having to package up the two columns into a pseudo-row and then sorting those row values. It sorts on the column values directly. This direct sorting is much faster, but more importantly it opens up the possibility to use an index, rather than a sort, to fulfill the ORDER BY.

Then if you create an index on (followers desc, id), your query can step through that index looking for rows that satisfy the @@ condition, stopping once it finds 50 of them. Doing it this way could be much faster than pulling out over 100,000 rows that are @@ matches and sorting them to pull out the top 50.

2

The "culprit" here takes up less than 1/4 of the total time. The real bottleneck is Index Only Scan using instagram_categories_username_category_agg, which takes 0.200 * 118453 = 23690.6 ms, almost 24 seconds, which is most of the time.

It looks like every user has exactly one category, unless that is just an amazing coincidence, so why is there a separate table for user category rather than it being an attribute of profiles_1000? That design appears to be the true culprit.

Anyway, the reason it has to do the Bitmap Heap Scan is because that is how it arrives at the correct answer. If it only did the Bitmap Index Scan, it wouldn't have any data about the matching rows, just their address. And it also wouldn't know if that address was really to a matching row, because of visibility, rechecks, and lossy bitmap compression.

Regular index scans also visit both the index and the table, they just don't separate out those operations as two different entries in the EXPLAIN plan the way a bitmap scan does.

1
  • Thanks for the detailed reply! Indeed I was misreading the output of the explain command. Still, after removing the left outer join the query still takes a whopping 13 seconds. It seems that now the full-text search is taking the most time. I am surprised that text search performance is this bad. Still, I think I have followed the documentation: I have a trigger that updates the tsv column with the vector information and a GIN index on it... Any ideas? I have edited my question with the new explain output.
    – rubik
    Sep 11 '18 at 15:10
1

Whatever else you do, you can largely simplify your query:

SELECT p.*, c.categories
FROM  (
   SELECT *
   FROM   instagram.profiles_1000
   WHERE  tsv @@ plainto_tsquery('arts')
   AND    followers > 0
   ORDER  by followers DESC, id  -- untangled
   LIMIT  50
   ) p
LEFT  JOIN instagram.profile_categories_agg c USING (username);

I removed the gratuitous self-join (which is just noise, id being the PK)

I also removed the clause:

AND    followers is not null  -- redundant

which is also redundant while you have AND followers > 0.

This will probably only yield minor performance improvements, though.

A partial index might help some more:

CREATE INDEX IF NOT EXISTS instagram_textsearch_index_1000_partial
ON instagram.profiles_1000 USING GIN(tsv);
WHERE followers > 0;

You can squeeze out some more overall performance by migrating to this table definition:

CREATE TABLE instagram.profiles_1000 (
    id          SERIAL PRIMARY KEY,
    followers   INT,  -- nobody has > 2^31 followers
    username    VARCHAR(255) NOT NULL UNIQUE, -- why VARCHAR(255)?
    tsv         TSVECTOR
);

Assuming a plain integer easily covers the maximum number of followers.

Then I moved the column in 2nd place to avoid wasting space with alignment padding. See:

And you should be using id instead of the username in table instagram.profile_categories_agg, which would both make the table smaller and the join faster.

Finally, you are are not using weights. For the purpose of this query, you work with a much simpler tsvector function resulting in smaller table and index:

CREATE FUNCTION instagram_documents_search_trigger()
  RETURNS trigger AS
$func$
BEGIN
   NEW.tsv := to_tsvector(COALESCE(NEW.username        , '')
                || ' ' || COALESCE(NEW.full_name       , '')
                || ' ' || COALESCE(NEW.location_country, '')
                || ' ' || COALESCE(NEW.location_region , '')
                || ' ' || COALESCE(NEW.biography       , '')
                || ' ' || COALESCE(NEW.location_city   , ''));
  RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.