Hello
Let say we have a table with 6 million records. There are 16 integer columns and few text column. It is read-only table so every integer column have an index. Every record is around 50-60 bytes.
The table name is "Item"
The server is: 12 GB RAM, 1,5 TB SATA, 4 CORES. All server for postgres.
There are many more tables in this database so RAM do not cover all database.
I want to add to table "Item" a column "a_elements" (array type of big integers) Every record would have not more than 50-60 elements in this column.
After that i would create index GIN on this column and typical select should look like this:
select*from item where......and5<@ a_elements;
I have also second, more classical, option.
Do not add column a_elements to table item but create table elements with two columns:
This table would have around 200 mln records.
I am able to do partitioning on this tables so number of records would reduce to 20 mln in table elements and 500 K in table item.
The second option select looks like this:
select item.*
from item
leftjoin elements on(item.id_item=elements.id_item)
where....
and5= elements.id_element
I wonder what option would be better in performance point of view. Is postgres able to use many different indexes with index GIN (option 1) in a single query ?
I need to make a good decision because import of this data will take me a 20 days.