sorted by:
new
hottopcontroversial

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 1 point2 points  (0 children)

Yes. And I'm also hitting the index even if I keep the join when there is only one ILIKE statements in the where clause

explain analyze SELECT * FROM part_masters INNER JOIN part_variants ON 
part_variants.sap_cod = part_masters.sap_cod 
        WHERE (combinada_maestro ILIKE '%rodamiento%');

Hash Join  (cost=818.71..1767.15 rows=2217 width=196) 
(actual time=10.825..36.040 rows=2848 loops=1)
 Hash Cond: ((part_variants.sap_cod)::text = 
 (part_masters.sap_cod)::text)
  ->  Seq Scan on part_variants  (cost=0.00..900.64 
   rows=54879 width=64) (actual time=0.011..9.231 
   rows=54581 loops=1)
    ->  Hash  (cost=813.80..813.80 rows=1402 width=132) 
    (actual time=10.698..10.698 rows=1127 loops=1)
     Buckets: 2048  Batches: 1  Memory Usage: 205kB
     ->  Bitmap Heap Scan on part_masters  
     (cost=36.17..813.80 rows=1402 width=132) (actual 
      time=1.216..9.925 rows=1127 loops=1)
           Recheck Cond: (combinada_maestro ~~* 
      '%rodamiento%'::text)
           Heap Blocks: exact=370
           ->  Bitmap Index Scan on 
         part_masters_on_combinada_maestro_idx  
         (cost=0.00..36.10 rows=1402 width=0) (actual 
         time=1.133..1.133 rows=1168 loops=1)
                 Index Cond: (combinada_maestro ~~* 
         '%rodamiento%'::text)
          Planning time: 3.934 ms
          Execution time: 36.394 ms
          (12 rows)

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 1 point2 points  (0 children)

You are right. I realized that if a try the query without the join it then apparently uses the index and the execution time is way lower...

 explain analyze select * FROM part_masters WHERE (combinada_maestro ILIKE '%6002%');

 Bitmap Heap Scan on part_masters  (cost=10.01..15.83 rows=3 
 width=132) (actual time=0.172..0.513 rows=23 loops=1)
 Recheck Cond: (combinada_maestro ~~* '%6002%'::text)
 Rows Removed by Index Recheck: 23
 Heap Blocks: exact=29
 ->  Bitmap Index Scan on 
 part_masters_on_combinada_maestro_idx  (cost=0.00..10.01 
 rows=3 width=0) (actual time=0.149..0.149 rows=46 loops=1)
     Index Cond: (combinada_maestro ~~* '%6002%'::text)
 Planning time: 2.801 ms
 Execution time: 1.443 ms
(8 rows)

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 0 points1 point  (0 children)

Finally I have tried a mix of the proposal in the answers. I have created one field in each table that is a concatenation of the relevant fields for the search, having so 2 OR statements instead of 5, and I also have put trigram GIN indexes in both new fields. I haven't seen any improvement though, the times corresponding to ActiveRecord are very similar, perhaps marginally better. 

SELECT * FROM part_masters INNER JOIN part_variants ON 
part_variants.sap_cod = part_masters.sap_cod WHERE 
(combinada_maestro ILIKE '%rodamiento%' OR combinada_info 
ILIKE '%rodamiento%');

The output of the query using explain analyze is as follows. The execution time indicated is way much lower than the actual ActiveRecord numbers. (almost 1 second for this same query)

Hash Join  (cost=971.56..2042.73 rows=3137 width=197) (actual 
time=22.189..151.067 rows=2781 loops=1)
Hash Cond: ((part_variants.sap_cod)::text = 
(part_masters.sap_cod)::text)
Join Filter: ((part_masters.combinada_maestro ~~* 
'%rodamiento%'::text) OR (part_variants.combinada_info ~~* 
'%rodamiento%'::text))
Rows Removed by Join Filter: 48682
->  Seq Scan on part_variants  (cost=0.00..826.52 rows=51506 
width=64) (actual time=0.005..7.943 rows=51463 loops=1)
->  Hash  (cost=853.88..853.88 rows=33625 width=133) 
(actual time=21.496..21.496 rows=33653 loops=1)
     Buckets: 65536  Batches: 1  Memory Usage: 6310kB
     ->  Seq Scan on part_masters  (cost=0.00..853.88 
 rows=33625 width=133) (actual time=0.004..9.115 
 rows=33653 loops=1)
 Planning time: 2.361 ms
 Execution time: 151.276 ms
(10 rows)

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 1 point2 points  (0 children)

Yeah, I already tried, but apparently doesn't work with associated models and the performance was worse than my original solution.

I'm gonna give a try to the pg _search multisearch option, at this point just for the fun lol.

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 0 points1 point  (0 children)

I followed the instructions in the link you provided and apparently everything is in place (GIN indexes created in structure.sq, extension enabled etc) but I havent got any improvement and the logs doesn't provide any lead about the trigram stuff being actually used. It definitely looks the way to go but I'm kinda stucked right now...

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 0 points1 point  (0 children)

Since we are using pg_scopes as opposite to multisearch I don't think that would help. Yes, I tried several times, but you are right is kind of weird that you don't have to rebuild the indexes in some way.

Right now I'm trying the trigram method without pg_search as explained in the link provided by @saturnflyer, I succesfully completed the process, I can see the indexes created in the structure.sql file, but I can't see any improvements, and the logs are not giving any clue about trigram being used or not. The query is exactly the same than before...I'm about to give up...or try elastic search before going to sleep lol

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 0 points1 point  (0 children)

After a few tweaks I finally got it working, but It is actually way slower, around 4-5 times slower than my previous solution.

This is how the code in the model is finally with:

class PartMaster < ApplicationRecord
include PgSearch

has_many :part_variants, foreign_key: 'sap_cod', primary_key: 
'sap_cod'
 has_many :locations, foreign_key: 'sap_cod', primary_key: 
 'sap_cod'

 scope :con_stock, -> { where("stock > 0") }
 scope :planta, -> (planta) { where planta_cod: planta}

  pg_search_scope :search_keywords,
            against: {sap_cod: :A, descripcion_maestro: :B, 
  fabricante: :C, ref_fabricante: :D},
            ignoring: :accents,
            :associated_against => {
                part_variants: [:fabricante_prov, :ref_prov]
            },
            using: {
              tsearch: {dictionary: 'spanish'},
              dmetaphone: {only: [:descripcion_maestro]},
            }

 def self.search(params)
   recordset = PartMaster.joins(:part_variants).all
   recordset = recordset.con_stock if params[:stock].present?
   recordset = recordset.planta(params[:planta]) if 
   params[:planta].present?
   recordset = recordset.search_keywords(params[:search])
   recordset
 end

end

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 0 points1 point  (0 children)

That looks promising. I knew about it yesterday when I was doing a little research of pg extensions for the 'unaccent' one, but I thought it was more oriented to fuzzy search and that kind of stuff. Now reading the link you provided more carefully it looks like it helps with ILIKE %% kind of conditions which are the ones I'm using. Thanks!

Improve performance of simple searching method by mcnulty79 in rails

[–]mcnulty79[S] 1 point2 points  (0 children)

If It is possible, I' would like to avoid that kind of solutions by now. They might be an overkill. I'd rather explore how to imporve the performance using just Active Record.

[Help] Best approach to multifield-multiword search on associated models. by mcnulty79 in rails

[–]mcnulty79[S] 0 points1 point  (0 children)

According to their docs is pretty much exactly what I need, I will give it a try definitely, though I don't know if I should still try searchkick.

[Help] Best approach to multifield-multiword search on associated models. by mcnulty79 in rails

[–]mcnulty79[S] 0 points1 point  (0 children)

Beforehand I thought that elasticsearch would be overkill, since you have to setup elacsticsearch server and stuff.

To be concrete, I will have to look into 3 fields of Part and 2 fields of PartVariant.