1

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.

Four queries.

Aditional details

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.