# Designing a table with precalculated team values for fast read queries

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.

Here's another query which takes around 22 seconds every time I run it.

``````yufinebotdev=# EXPLAIN ANALYZE SELECT * FROM CampingCombinations WHERE team @> ARRAY['tenebria'] ORDER BY total_morale DESC LIMIT 50;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=175530.62..175536.45 rows=50 width=81) (actual time=22710.401..22737.688 rows=50 loops=1)
->  Gather Merge  (cost=175530.62..209098.89 rows=287708 width=81) (actual time=22710.400..22737.680 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Sort  (cost=174530.59..174890.23 rows=143854 width=81) (actual time=21644.412..21644.416 rows=42 loops=3)
Sort Key: total_morale DESC
Sort Method: top-N heapsort  Memory: 38kB
Worker 0:  Sort Method: top-N heapsort  Memory: 37kB
Worker 1:  Sort Method: top-N heapsort  Memory: 37kB
->  Parallel Bitmap Heap Scan on campingcombinations  (cost=3187.69..169751.87 rows=143854 width=81) (actual time=195.021..21556.332 rows=119253 loops=3)
Recheck Cond: (team @> '{tenebria}'::text[])
Heap Blocks: exact=53792
->  Bitmap Index Scan on idx_team  (cost=0.00..3101.38 rows=345250 width=0) (actual time=520.046..520.047 rows=357760 loops=1)
Index Cond: (team @> '{tenebria}'::text[])
Planning Time: 10.315 ms
Execution Time: 22737.779 ms
(16 rows)

Time: 22749.428 ms (00:22.749)
yufinebotdev=# EXPLAIN ANALYZE SELECT * FROM CampingCombinations WHERE team @> ARRAY['tenebria'] ORDER BY total_morale DESC LIMIT 50;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=175530.62..175536.45 rows=50 width=81) (actual time=26870.903..26891.772 rows=50 loops=1)
->  Gather Merge  (cost=175530.62..209098.89 rows=287708 width=81) (actual time=26870.901..26891.763 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Sort  (cost=174530.59..174890.23 rows=143854 width=81) (actual time=26761.067..26761.070 rows=36 loops=3)
Sort Key: total_morale DESC
Sort Method: top-N heapsort  Memory: 37kB
Worker 0:  Sort Method: top-N heapsort  Memory: 37kB
Worker 1:  Sort Method: top-N heapsort  Memory: 38kB
->  Parallel Bitmap Heap Scan on campingcombinations  (cost=3187.69..169751.87 rows=143854 width=81) (actual time=82.575..26628.228 rows=119253 loops=3)
Recheck Cond: (team @> '{tenebria}'::text[])
Heap Blocks: exact=52218
->  Bitmap Index Scan on idx_team  (cost=0.00..3101.38 rows=345250 width=0) (actual time=130.074..130.075 rows=357760 loops=1)
Index Cond: (team @> '{tenebria}'::text[])
Planning Time: 0.259 ms
Execution Time: 26891.849 ms
(16 rows)
``````

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?

• As I understand each group of attrubutes contains strongly 4 different (?) attributes from some attributes list. Is it? If yes - what is total count of possible attributes values? – Akina Apr 9 '19 at 5:08
• @Akina There's a total of 131 attributes (or characters) total. And each group (or team) consists of 4 attributes no more no less. The only thing that would vary is the amount of attributes that I'm searching for on the table which could be between 1 and 3 attributes. – Dimbreath Apr 9 '19 at 5:29