[Issue] Why GiST index on signature not working

[mickeyhaha]

hi Akorotkov,

I followed the guide for https://github.com/postgrespro/imgsmlr and it works well, just one issue: when I explain the query, it shows Seq Scan instead of Index Scan, meaning it's not using the GiST index to query the data.

postgres=# explain select t1.sig <-> t2.sig from train2_pat_perf2 t1 join train2_pat_perf2 t2 on t1.id <> t2.id and t1.sig <-> t2.sig <0.5 order by t1.sig <-> t2.sig desc;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Sort  (cost=40289112.51..40547084.53 rows=103188810 width=128)
   Sort Key: ((t1.sig <-> t2.sig)) DESC
   ->  Nested Loop  (cost=0.00..6450626.41 rows=103188810 width=128)
         Join Filter: ((t1.id <> t2.id) AND ((t1.sig <-> t2.sig) < '0.5'::double precision))
         ->  Seq Scan on train2_pat_perf2 t1  (cost=0.00..464.95 rows=17595 width=72)
         ->  Materialize  (cost=0.00..552.93 rows=17595 width=72)
               ->  Seq Scan on train2_pat_perf2 t2  (cost=0.00..464.95 rows=17595 width=72)
(7 rows)

Would you help me? Thanks!

Best regards! Jason

[akorotkov]

From @mickeyhaha email

found the issue, we need to contain both 'order by t1.sig <-> t2.sig' and 'limit 100' in the query to enable the index, thank you all the same.

Right, imgsmlr indexes supports KNN only. So, you've to add LIMIT in order to benefit from indexes.