Postgresql – split big table into two joined view

postgresqltableview

i have a big postgres db with single table for product and attribute but i want two view of one with product and one with attribute.

for example now i have

tb_products

iditem | name | attributes
-----------------------
1      | art1 | a
2      | art1 | b    
3      | art1 | c
4      | art1 | d

what i need is

vs_product

iditem | name 
-----------------------
1      | art1 

vs_attrib

iditem |  attributes
-----------------------
1      | a
1      | b   
1      | c
1      | d

what i have tried is something like this and it seem to work, but is too slow when extractiong attributes

vs_product

SELECT
Min(.tb_products."id") AS "Product ID",
FROM
tb_products
GROUP BY
tb_products.item

vs_attrib

SELECT
a1."id" AS "oldid",
(
SELECT MIN
    ( a2."id" )  
FROM
    tb_products a2
WHERE 
    a1.name = a2.name
GROUP BY
    a1.name 
) AS "newid"
FROM
tb_products AS a1

any ideas?

Best Answer

only the second, can you be more specific?

For second view test

CREATE VIEW vs_attrib 
AS
SELECT MIN(iditem) OVER (PARTITION BY name) iditem, attributes
FROM tb_products 

Index tb_products (name, iditem) may increase view's performance.