PostgreSQL multidimensional array search

Question

Hi Guys,

I am a newbie to Postgresql and was trying with it.

I have created a simple table:

CREATE table items_tags ( 
ut_id SERIAL Primary KEY,       
item_id integer,   
item_tags_weights text[]  
);                                

where: item_id - Item Id with these tags are associated item_tags_weights - Tags associated with Itm including weight

Example entry:
--------------------
  ut_id  | item_id |                                                                                  item_tags_weights  
---------+---------+-------------------------------------------------------------------------------------------------------------------------------
       3 |       2 | {{D,1},{B,9},{W,3},{R,18},{F,9},{L,15},{G,12},{T,17},{0,3},{I,7},{E,14},{S,2},{O,5},{M,4},{V,3},{H,2},{X,14},{Q,9},{U,6},{P,16},{N,11},{J,1},{A,12},{Y,15},{C,15},{K,4},{Z,17}}
 1000003 |       3 | {{Q,4},{T,19},{P,15},{M,14},{O,20},{S,3},{0,6},{Z,6},{F,4},{U,13},{E,18},{B,14},{V,14},{X,10},{K,18},{N,17},{R,14},{J,12},{L,15},{Y,3},{D,20},{I,18},{H,20},{W,15},{G,7},{A,11},{C,14}}
       4 |       4 | {{Q,2},{W,7},{A,6},{T,19},{P,8},{E,10},{Y,19},{N,11},{Z,13},{U,19},{J,3},{O,1},{C,2},{L,7},{V,2},{H,12},{G,19},{K,15},{D,7},{B,4},{M,9},{X,6},{R,14},{0,9},{I,10},{F,12},{S,11}}
       5 |       5 | {{M,9},{B,3},{I,6},{L,12},{J,2},{Y,7},{K,17},{W,6},{R,7},{V,1},{0,12},{N,13},{Q,2},{G,14},{C,2},{S,6},{O,19},{P,19},{F,4},{U,11},{Z,17},{T,3},{E,10},{D,2},{X,18},{H,2},{A,2}}
(4 rows)

where: {D,1} - D = tag, 1 = tag weight

Well, I just wanted to list the items_id where tags = 'U' according tag weight.

On way is to select ALL the tags from database and do the processing in high-level language with sort and use the result set.

For this, I can do the following:

1) SELECT * FROM user_tags WHERE 'X' = ANY (interest_tags_weights)

2) Extract and sort the information and display.

But considering that multiple items can be associated with a single 'TAG', and assuming 10 million entry, this method will be surely sluggish.

Any idea to list as needed with CREATE function or so?

Any pointers will be helpfull.

Many thanks.

Answer 1

Have you considered normalization, i.e. moving the array field into another table? Apart from being easy to query and extend, it's likely to have better performance on larger databases.