StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POProper full text index Rails/PostgreSQL/pg_search
text
Body
copied!<p>I am testing performance for PostgreSQL full text search (using pg_search gem) and solr (sunspot_solr gem).</p> <p>For 4 million records I am getting <strong>13456 ms for Tsearch</strong> and <strong>800 ms with SOLR</strong> (that is SOLR query + DB retrival). It is obvious that I need index but I am not sure how to create one for full text search. I investigated and found that for full text search I should use GIN index.</p> <pre><code>execute "CREATE INDEX products_gin_title ON products USING GIN(to_tsvector('english', title))" </code></pre> <p>But I am searching via two more columns and I need multi-value index and I am not sure how to implement it? I am not very familiar with DB part. My search code looks like:</p> <pre><code>@results = Product.search_title(params[:search_term]).where("platform_id=? AND product_type=?", params[:platform_id], params[:type_id]).limit(10).all </code></pre> <p><em>How do I create proper query for this type of situations?</em></p> <p>Here is SQL output from rails for search term <strong>car</strong>.</p> <pre><code>Product Load (12494.0ms) SELECT "products".*, ( ts_rank((to_tsvector('simple', coalesce("products"."title"::text, ''))), (to_ tsquery('simple', ''' ' || 'car' || ' ''')), 2) ) AS pg_search_rank FROM "products" WHERE (((to_tsvector('simple', coalesce("products"."tit le"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'car' || ' ''')))) AND (platform_id='26' AND product_type='2') ORDER BY pg_search_rank DESC, "products"."id" ASC LIMIT 10 </code></pre> <p>EDIT:</p> <p>I am using PostgreSQL 8.4.11, <code>EXPLAIN ANALYZE</code> output is following.</p> <pre><code>Limit (cost=108126.34..108126.36 rows=10 width=3824) (actual time=12228.736..12228.738 rows=10 loops=1) -> Sort (cost=108126.34..108163.84 rows=14999 width=3824) (actual time=12228.733..12228.734 rows=10 loops=1) Sort Key: (ts_rank(to_tsvector('simple'::regconfig, COALESCE((title)::text, ''::text)), '''car'''::tsquery, 2)), id Sort Method: top-N heapsort Memory: 18kB -> Seq Scan on products (cost=0.00..107802.22 rows=14999 width=3824) (actual time=7.532..12224.585 rows=977 loops=1) Filter: ((platform_id = 26) AND (product_type = 2) AND (to_tsvector('simple'::regconfig, COALESCE((title)::text, ''::text)) @@ '''car'''::tsquery)) Total runtime: 12228.813 ms </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload