Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

... WHERE
      (to_tsvector('english', title) @@ to_tsquery('english','foo'))
    OR
      (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.

share|improve this question
    
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 '12 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 '12 at 16:40
add comment

1 Answer 1

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.

share|improve this answer
    
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 '12 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 :-) –  Jack Douglas Mar 23 '12 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 '12 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 :-) –  Jack Douglas Mar 24 '12 at 8:44
    
Jack :) I'll bite - subscribed. –  user1593 Mar 25 '12 at 12:12
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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