Consider indexing discriminator columns

[mtbc]

A GIN index may work rather well when there are few distinct values on a column. (Could possibly verify by trying a GIN index on eventlog.action.)

[joshmoore]

Just ran into this while looking for masks on IDR.

cc: @sbesson @manics

[joshmoore]

on idr-testing:

idr=# \timing
Timing is on.
idr=# create index shape_discriminator on shape using gist(discriminator);
ERROR:  data type character varying has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
Time: 17.133 ms
idr=# create extension btree_gist;
CREATE EXTENSION
Time: 1016.033 ms (00:01.016)
idr=# create index shape_discriminator on shape using gist(discriminator);
CREATE INDEX
Time: 132258.252 ms (02:12.258)
idr=# select count(m) from shape m where m.discriminator = 'mask';
  count
---------
 1308091
(1 row)

Time: 2046.354 ms (00:02.046)

Vastly faster, but still no images I'm looking for:

idr=# select i.id, count(m) from image i, roi r, shape m where i.id = r.image and r.id = m.roi and m.discriminator = 'mask' and i.series > 0 group by i.id;
 id | count
----+-------
(0 rows)

Time: 7032.757 ms (00:07.033)

[mtbc]

👍 Further, as well as the btree_gin extension, could even consider going as far as the pg_trgm extension to enable USING GIN(name, LOWER(value) gin_trgm_ops) for indexing columns that should support infix string search, e.g., for mapr.

[mtbc]

infix string search

e.g., for IDR, update_yeast_genes.py has infix search on map annotation values.