PostgreSql: how to create index for character varying array type column
<p>My table structure is like </p>

<pre><code>CREATE TABLE Product
(
  id bigserial NOT NULL,
  seller_id integer,
  product_data  character varying[],
  ptype integer,
  CONSTRAINT config_pkey PRIMARY KEY (id)
)
</code></pre>

<p>Index created: 
<code>CREATE INDEX product_name_idx ON Product USING gin (product_data);</code></p>

<p>proudct_data column can have around 50 different product names at max in the array and the Product table has around 1M <strong>UNIQUE</strong> rows.
I need to find out seller_id s which have 'steel' as a product, where 'steel' may be a sub-string of the product name in product_data;</p>

<p>Currently I am using the following query:</p>

<pre><code> SELECT * FROM Product WHERE ptype in ( 2,3 ) and '%steel%' % any(product_data) offset 0 limit 10;
</code></pre>

<p>The above query gives the expected results, but as it is not using the <code>product_name_idx</code> index, its very slow. 
How can I create proper index on this column? Please help me out.</p>