I have table with 500 000 rows, I have int[] column "my_array" in this
table, this array column contains minimum 1 and maximum 5 different values.
I have GIN index on my_array column:
* "CREATE INDEX idx ON table_name USING GIN(my_array gin__int_ops)"*
Then I use this query: "*SELECT * FROM table_name WHERE my_array @>
'{3}'::integer[] ORDER BY id LIMIT 50"*
Execution time of this query is approximately 500-1000 ms. Then if I drop
gin index "*idx*", query works extremely fast, less than 20 ms.
But, if I search value, which does not exists at all, for example no one
array not contains number "77" and I search: * "WHERE my_array @>
'{77}'::integer[]" *, then using gin index is much better and fast, (less
than 20 ms), but without index, query takes 500-1000 ms.
So, what to do? For values which does not in any one rows, using index is
much better, but for values, which are at least in several rows, using
index, slows down performance.
Can somehow make, that searching was always fast (when value exist in array
and when not)
