Hello
Let say we have a table with 6 million records. There are 16 integer column=
s and few text column. It is read-only table so every integer column have a=
n 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 databas=
e.
I want to add to table "Item" a column "a_elements" (array type of big inte=
gers) 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 shoul=
d 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 t=
wo columns:
id_itemid_elementThis table would have around 200 mln records.
I am able to do partitioning on this tables so number of records would redu=
ce to 20 mln in table elements and 500 K in table item.
The second option select looks like this:
select item.*=20
from item=20
=C2=A0 =C2=A0 leftjoin elements on(item.id_item=3Delements.id_item)=20
where....=20
and5=3D elements.id_element
I wonder what option would be better in performance point of view. Is postg=
res able to use many different indexes with index GIN (option 1) in a singl=
e query ?
I need to make a good decision because import of this data will take me a 2=
0 days.=
