CODEWITHSUNDEEP
ruby-on-railspostgresql
Metaphysiker
Metaphysiker

Reputation: 1093

Rails and Postgresql, confused about building indexes

I started to use pg_search in my rails project and I'm confused about building indexes.

I have a model with two attributes, where I do a full-text search: title and description. Currently, I have this setup:

class Argumentation < ApplicationRecord
  include PgSearch
  pg_search_scope :searchfor, :against => [:title, :description]
end

This works but it the query is slow. It needs to be faster and my first thought was to add an index.

There is a site dedicated to building indexes for full-text search: https://github.com/Casecommons/pg_search/wiki/Building-indexes

I want to follow the guide, but some things confuse me:

1.The guide tells me, to add a column "tsvector" to the table.

add_column :posts, :tsv_body, :tsvector

But isn't this line of code adding two columns: tsv_body and tsvector? Or is tsv_body a placeholder, for example description or title in my case?

  1. After that, the index should be added to tsv_body like this:

    add_index(:posts, :tsv_body, using: 'gin')

Then, the guide talks about triggers. Why are triggers needed? If you add an index to a string attribute, triggers aren't needed (if I'm not mistaken).

  1. In the trigger itself, there is "pg_catalog.english".

create_trigger(compatibility: 1).on(:posts).before(:insert, :update) do "new.tsv_body := to_tsvector('pg_catalog.english', coalesce(new.body,''));" end

Since there are many languages in my attributes, not just english, I wonder: Is it even possible to add an index, if there are multiple languages?

Upvotes: 0

Views: 146

Answers (1)

Taryn East
Taryn East

Reputation: 27747

0) You should ask separate questions as separate questions ;)

1) add_column :posts, :tsv_body, :tsvector

isn't this line of code adding two columns: tsv_body and tsvector?

No - add_column has several arguments for adding a single column. The first argument is the table-name, the second is the name of the new column, the third is the type of column (eg string, integer, or in this case tsvector)

2) Why are triggers needed?

A trigger is something that watches for something to happen in your database, then does something based on that. In this case, your trigger will probably be doing something like watching to see when one of your columns gets changed... and it will then go ahead and update the index with the changes - this is better than periodically running the rebuild-index over the entire table - which potentially is huge and will take a long time and will be "updating" things that didn't actually change.

3) Is it even possible to add an index, if there are multiple languages?

Yes. I'm not highly familiar with the details - but the fact that you add the language name for the index is indicative that you can have an index for the other languages too.

Upvotes: 1

Related Questions