CREATE TABLE products(
products_id serial,
location_id integer, --(not unique)
idle_products jsonb --has 1000 elements ('[13,45,8976.....]'::jsonb)
);
CREATE EXTENSION btree_gin;
CREATE INDEX idx ON products USING GIN(location_id, idle_products);
Take just for example;
products
table have millions of rows and 1000 elements in every row in idle_products
column.
SELECT * FROM products
WHERE location_id IN (SELECT * FROM unnest(array[1245,456,6342,2,23453]::int[])) AND
idle_products @> to_jsonb(222) ;
On that query what i want to learn is:
Is idle_products
being searched through only these 5 rows by composite gin index and if so, does that mean much more performance gain(because there are only 5x1000 elements in 5 rows in idle_products
column,
i mean it would only search through 5 rows using gin composite index) or is idle_products
being searched through all rows in products table (despite filtering against location_id
) and then reduce(limit) rows to 5
(array[1245,456,6342,2,23453]::int[]
has 5 five location_id
) by filtering location_id
values?