I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns.

The postgres docs talk about creating a ts_vector index on concatenated columns, like so:

CREATE INDEX pgweb_idx ON pgweb 
    USING gin(to_tsvector('english', title || ' ' || body));

which I can search like so:

... WHERE 
      (to_tsvector('english', title||' '||body) @@ to_tsquery('english', 'foo'))

However, if I wanted to sometimes search just the title, sometimes just the body, and sometimes both, I would need 3 separate indexes. And if I added in a third column, that could potentially be 6 indexes, and so on.

An alternative which I haven't seen in the docs is just to index the two columns seperately, and then just use a normal WHERE...OR query:

      (to_tsvector('english', title) @@ to_tsquery('english','foo'))
      (to_tsvector('english', body) @@ to_tsquery('english','foo'))

Benchmarking the two on ~1million rows seems to have basically no difference in performance.

So my question is:

Why would I want to concatenate indexes like this, rather than just indexing columns individually? What are the advantages/disadvantages of both?

My best guess is that if I knew in advance I would only want to ever search both columns (never one at a time) I would only ever need one index by concatenating which use less memory.

  • I'm not really certain how concatenating the title into the body and then indexing that would give much value, though I am open to correction. I would probably just stick with indexing them separately. Also, if it was some wacky one-off that somehow required you to concatenate, then I guess you could just run the query ad-hoc.
    – swasheck
    Mar 22, 2012 at 15:49
  • You're correct in your guess. I would encourage you to self-answer if no-one else does, Jeopardy style here.
    – jcolebrand
    Mar 22, 2012 at 16:40

2 Answers 2


No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');

Actually the alternative would be to use where with OR, and not AND.

If you have index on tsvector(body + title), and you're searching in it, searched words can be in title OR in body.

Also - when testing, make sure you have reasonable number of rows in the table.

Simplest case which should show good difference: find two words - one of them that is very likely to be in title. and the other - that is very likely to be in body. But make sure that there is not much of rows that match both criteria. For example - you might have 30% of word "depesz" being in body. You also have ~ 30% chance of having "mysql" in title. But having "depesz and mysql" in any of the fields in the same row is very unlikely. And then check performance with such indexes.

  • Ha, good spot, on OR vs AND i'll update the question. I did it with 1Million rows - couldn't be bothered waiting for any more to insert :)
    – latentflip
    Mar 23, 2012 at 15:02
  • 1
    Thanks for dropping by depesz - we are getting quite a few postgres questions these days so I do hope you'll stick around :-) Mar 23, 2012 at 16:26
  • @Jack: not sure I will - I found stackexchange sites less and less usable. I generally try to get RSS, but on stackexchange sites rss is pretty much useless - so much pollution from edition of old questions.
    – user1593
    Mar 23, 2012 at 19:50
  • I've created an rss feed for you here - are you willing to give that a try? I'm happy to put the effort in filtering out stuff you are unlikely to be interested in to gain the chance to have you more involved in the site :-) Mar 24, 2012 at 8:44
  • Jack :) I'll bite - subscribed.
    – user1593
    Mar 25, 2012 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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