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.

Four queries.

EDIT 1: 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 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 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.