Timeline for Searching in Array performance?
Current License: CC BY-SA 3.0
12 events
when toggle format | what | by | license | comment | |
---|---|---|---|---|---|
Jan 25, 2018 at 11:37 | comment | added | simo | Kindly check my db design question here: dba.stackexchange.com/questions/196207/… | |
Jan 25, 2018 at 9:51 | comment | added | simo | so, if an array has 3000 id, I will have to create 3000 rows as an alternative, the table size will get very big in a short time I believe, right? each time a message is sent, I will have to add thousands of rows.. is that still ok? | |
Jan 25, 2018 at 9:10 | vote | accept | simo | ||
Jan 25, 2018 at 9:06 | comment | added | Jack Douglas |
Have another table id,parent_id which has one row for each of the elements of your current array, so the primary key of the new table is id,parent_id . Then drop the array from your current table. That's the basic idea if you can redesign — then you will be using the normal relational features of the database such as joins and regular indexes, which are the right tools for most jobs.
|
|
Jan 25, 2018 at 8:46 | comment | added | simo | @JackDouglas will you please describe more? I am not getting your point | |
Jan 24, 2018 at 16:50 | answer | added | Evan Carroll | timeline score: 2 | |
Jan 24, 2018 at 15:49 | comment | added | TomTom | Index? There does not seem to be an index on the field. Not a postgres guy, but on SQL Server a foreign key constraint does NOT automatically create an index. | |
Jan 24, 2018 at 15:10 | comment | added | simo | Kindly check the structure above | |
Jan 24, 2018 at 15:10 | history | edited | simo | CC BY-SA 3.0 |
added 1289 characters in body
|
Jan 24, 2018 at 11:56 | comment | added | Jack Douglas | Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements. | |
Jan 24, 2018 at 11:51 | comment | added | user1822 |
Please edit your question and add the create table statement for the table in question (including all indexes), the query you are using and the execution plan generated using explain (analyze, buffers) . Formatted text please, no screen shots. Edit your question. Do not post code in comments
|
|
Jan 24, 2018 at 11:19 | history | asked | simo | CC BY-SA 3.0 |