Sfrgttk

Could Giant Ground Sloths have been a good pack animal for the ancient Mayans?

If a centaur druid Wild Shapes into a Giant Elk, do their Charge features stack?

Are cabin dividers used to "hide" the flex of the airplane?

Ideas for 3rd eye abilities

Re-submission of rejected manuscript without informing co-authors

What's the difference between repeating elections every few years and repeating a referendum after a few years?

Why do UK politicians seemingly ignore opinion polls on Brexit?

How is it possible for user's password to be changed after storage was encrypted? (on OS X, Android)

Is there a name of the flying bionic bird?

Prime joint compound before latex paint?

Symmetry in quantum mechanics

I see my dog run

Can I legally use front facing blue light in the UK?

Information to fellow intern about hiring?

Are objects structures and/or vice versa?

Is ipsum/ipsa/ipse a third person pronoun, or can it serve other functions?

LWC and complex parameters

How to answer pointed "are you quitting" questioning when I don't want them to suspect

When blogging recipes, how can I support both readers who want the narrative/journey and ones who want the printer-friendly recipe?

Is it wise to focus on putting odd beats on left when playing double bass drums?

What does "enim et" mean?

How to make payment on the internet without leaving a money trail?

Landlord wants to switch my lease to a "Land contract" to "get back at the city"

Why did the Germans forbid the possession of pet pigeons in Rostov-on-Don in 1941?

Designing a table with precalculated team values for fast read queries

PostgreSQL - Return unique combinations of columns based on where clausePostgreSQL, integer arrays, index for equalityWould index lookup be noticeably faster with char vs varchar when all values are 36 charsDesigning a High Score/Leaderboard tableWhat are the pros and cons of storing two values in an array instead of two columns?Performance of a single table versus single table with jsonb versus multiple tables?Query Postgres JSON array for values in textHow to optimize for a 50million+ row table read with SELECT statement?Search for nested values in jsonb array with greater operatorWhy full text search on table with GIN index is still very slow

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

My version of PostgreSQL is 10.7.

As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a table of my database. Write performance doesn't matter in this case since the table will be read-only and with occasional writes once per day every week.

I currently generate all possible 4 character team combinations using Python, the order of the characters doesn't matter as the result of the team calculation will be the same.

The users would input between 1 to 3 characters and I would query for a team that contains all those character that the user input, sort them by the total_morale column and return 50 of them.

For this I thought of two different approaches, both which I've tried and stumbled into problems:

Approach #1

On this attempt I created a table with five columns, 1 for each character and the result.

| character_1 | character_2  | character_3      | character_4 | total_morale |

|-------------|--------------|------------------|-------------|--------------|

| cidd        | tenebria     | watcher-schuri   | yufine      | 34           |

| cidd        | specimen-sez | specter-tenebria | tenebria    | 48           |

With indexes, this method turned out to be really fast, however there was an oversight. Let's assume an user wants a team with Cidd and Tenebria on it, both of the rows above are correct. However, how would I query this without knowing in which column each character is?

My approach to solve this was to generate a WHERE with every possible column combination but that drastically reduced the query performance to more than a few seconds.

Approach #2

Rather than having a column per character I thought of having a column with an array as the team and another column with the total_morale which resulted in something like:

| team {text[]}                                 | total_morale |

|-----------------------------------------------|--------------|

| {cidd,tenebria,watcher-schuri,yufine}         | 34           |

| {cidd,specimen-sez,specter-tenebria,tenebria} | 48           |

Then since I'm not really knowledgeable on PostgreSQL (or in databases in general) I looked how to index the team column and saw the GIN index for which I did:

CREATE INDEX idx_team ON CampingCombinations USING GIN(team);

I could query it to test it out with something like:

SELECT * FROM CampingCombinations WHERE team @> ARRAY['cidd'] 

ORDER BY total_morale DESC LIMIT 50;

Which gave me varying results, sometimes queries would take less than 120ms and other times it would take over 5 seconds for the same exact query after one minute. Had this same behavior on queries which contained multiple characters.

My question is, is it possible to optimize these queries (since it's only read-only database, where I only need to write once per week maybe once every two weeks) for fast reads and sorting? Or would I need a completely different schema to achieve this?

postgresql postgresql-10 python

share|improve this question

asked 32 mins ago

DimbreathDimbreath

11

New contributor

Dimbreath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

My version of PostgreSQL is 10.7.

As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a table of my database. Write performance doesn't matter in this case since the table will be read-only and with occasional writes once per day every week.

I currently generate all possible 4 character team combinations using Python, the order of the characters doesn't matter as the result of the team calculation will be the same.

The users would input between 1 to 3 characters and I would query for a team that contains all those character that the user input, sort them by the total_morale column and return 50 of them.

For this I thought of two different approaches, both which I've tried and stumbled into problems:

Approach #1

On this attempt I created a table with five columns, 1 for each character and the result.

| character_1 | character_2  | character_3      | character_4 | total_morale |

|-------------|--------------|------------------|-------------|--------------|

| cidd        | tenebria     | watcher-schuri   | yufine      | 34           |

| cidd        | specimen-sez | specter-tenebria | tenebria    | 48           |

With indexes, this method turned out to be really fast, however there was an oversight. Let's assume an user wants a team with Cidd and Tenebria on it, both of the rows above are correct. However, how would I query this without knowing in which column each character is?

My approach to solve this was to generate a WHERE with every possible column combination but that drastically reduced the query performance to more than a few seconds.

Approach #2

Rather than having a column per character I thought of having a column with an array as the team and another column with the total_morale which resulted in something like:

| team {text[]}                                 | total_morale |

|-----------------------------------------------|--------------|

| {cidd,tenebria,watcher-schuri,yufine}         | 34           |

| {cidd,specimen-sez,specter-tenebria,tenebria} | 48           |

Then since I'm not really knowledgeable on PostgreSQL (or in databases in general) I looked how to index the team column and saw the GIN index for which I did:

CREATE INDEX idx_team ON CampingCombinations USING GIN(team);

I could query it to test it out with something like:

SELECT * FROM CampingCombinations WHERE team @> ARRAY['cidd'] 

ORDER BY total_morale DESC LIMIT 50;

Which gave me varying results, sometimes queries would take less than 120ms and other times it would take over 5 seconds for the same exact query after one minute. Had this same behavior on queries which contained multiple characters.

My question is, is it possible to optimize these queries (since it's only read-only database, where I only need to write once per week maybe once every two weeks) for fast reads and sorting? Or would I need a completely different schema to achieve this?

postgresql postgresql-10 python

share|improve this question

asked 32 mins ago

DimbreathDimbreath

11

New contributor

Dimbreath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

My version of PostgreSQL is 10.7.

As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a table of my database. Write performance doesn't matter in this case since the table will be read-only and with occasional writes once per day every week.

I currently generate all possible 4 character team combinations using Python, the order of the characters doesn't matter as the result of the team calculation will be the same.

The users would input between 1 to 3 characters and I would query for a team that contains all those character that the user input, sort them by the total_morale column and return 50 of them.

For this I thought of two different approaches, both which I've tried and stumbled into problems:

Approach #1

On this attempt I created a table with five columns, 1 for each character and the result.

| character_1 | character_2  | character_3      | character_4 | total_morale |

|-------------|--------------|------------------|-------------|--------------|

| cidd        | tenebria     | watcher-schuri   | yufine      | 34           |

| cidd        | specimen-sez | specter-tenebria | tenebria    | 48           |

With indexes, this method turned out to be really fast, however there was an oversight. Let's assume an user wants a team with Cidd and Tenebria on it, both of the rows above are correct. However, how would I query this without knowing in which column each character is?

My approach to solve this was to generate a WHERE with every possible column combination but that drastically reduced the query performance to more than a few seconds.

Approach #2

Rather than having a column per character I thought of having a column with an array as the team and another column with the total_morale which resulted in something like:

| team {text[]}                                 | total_morale |

|-----------------------------------------------|--------------|

| {cidd,tenebria,watcher-schuri,yufine}         | 34           |

| {cidd,specimen-sez,specter-tenebria,tenebria} | 48           |

Then since I'm not really knowledgeable on PostgreSQL (or in databases in general) I looked how to index the team column and saw the GIN index for which I did:

CREATE INDEX idx_team ON CampingCombinations USING GIN(team);

I could query it to test it out with something like:

SELECT * FROM CampingCombinations WHERE team @> ARRAY['cidd'] 

ORDER BY total_morale DESC LIMIT 50;

Which gave me varying results, sometimes queries would take less than 120ms and other times it would take over 5 seconds for the same exact query after one minute. Had this same behavior on queries which contained multiple characters.

My question is, is it possible to optimize these queries (since it's only read-only database, where I only need to write once per week maybe once every two weeks) for fast reads and sorting? Or would I need a completely different schema to achieve this?

postgresql postgresql-10 python

share|improve this question

asked 32 mins ago

DimbreathDimbreath

11

New contributor

Dimbreath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

| character_1 | character_2  | character_3      | character_4 | total_morale |

|-------------|--------------|------------------|-------------|--------------|

| cidd        | tenebria     | watcher-schuri   | yufine      | 34           |

| cidd        | specimen-sez | specter-tenebria | tenebria    | 48           |

| team {text[]}                                 | total_morale |

|-----------------------------------------------|--------------|

| {cidd,tenebria,watcher-schuri,yufine}         | 34           |

| {cidd,specimen-sez,specter-tenebria,tenebria} | 48           |

CREATE INDEX idx_team ON CampingCombinations USING GIN(team);

SELECT * FROM CampingCombinations WHERE team @> ARRAY['cidd'] 

ORDER BY total_morale DESC LIMIT 50;

share|improve this question

asked 32 mins ago

DimbreathDimbreath

11

New contributor

Dimbreath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 32 mins ago

DimbreathDimbreath

11

New contributor

Dimbreath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 32 mins ago

DimbreathDimbreath

11

New contributor

Dimbreath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 32 mins ago

DimbreathDimbreath

11