EXPLAIN EXTENDED

How to create fast database queries

Happy New Year!

Comments enabled. I *really* need your comment

One of the best New Year presents I've ever got was a copy of the German-style board game, The Settlers of Catan.

This game has brought me and my friends many an hour of good entertainment.

Catan Players

The game is played on a hexagon field with 19 hexagon tiles (3 + 4 + 5 + 4 + 3), which have to be randomly put into appropriate places. In addition, 18 of those tiles have a score token on it, which has to be put there, also randomly, albeit with some limitations. Finally, 9 more pieces (harbors) have to be randomly put to their places, which are printed on the game field.

Today, we'll be implementing the Almanac Variable Catan setup using SQL.

Tiles

We will be using trapezoidal encoding for the tiles, so that each tile is defined by its position on two axes at 60° to each other, starting at the NW corner. The x axis is diagonal (SW to NE), the y axis is horizontal. The hexagons are pointy topped.

We will create a small CTE to define the coordinates of each tile:

WITH    tiles (rn, x, y) AS
        (
        VALUES
                (1, 0, 0),
                (2, 1, 0),
                (3, 2, 0),
                (4, 3, 1),
                (5, 4, 2),
                (6, 4, 3),
                (7, 4, 4),
                (8, 3, 4),
                (9, 2, 4),
                (10, 1, 3),
                (11, 0, 2),
                (12, 0, 1),
                (13, 1, 1),
                (14, 2, 1),
                (15, 3, 2),
                (16, 3, 3),
                (17, 2, 3),
                (18, 1, 2),
                (19, 2, 2)
        )
SELECT  *
FROM    tiles
rn x y
1 0 0
2 1 0
3 2 0
4 3 1
5 4 2
6 4 3
7 4 4
8 3 4
9 2 4
10 1 3
11 0 2
12 0 1
13 1 1
14 2 1
15 3 2
16 3 3
17 2 3
18 1 2
19 2 2

Resources

We'll be using / for wheat, # for wood, > for rock, " for sheep and . for clay. For the desert we will be using empty space. Those characters are not that descriptive but they make good distinct ASCII art patterns.

We will just put all available resources into a string then shuffle the string using ORDER BY RANDOM():

WITH    tiles (rn, x, y) AS
        (
        VALUES
                (1, 0, 0),
                (2, 1, 0),
                (3, 2, 0),
                (4, 3, 1),
                (5, 4, 2),
                (6, 4, 3),
                (7, 4, 4),
                (8, 3, 4),
                (9, 2, 4),
                (10, 1, 3),
                (11, 0, 2),
                (12, 0, 1),
                (13, 1, 1),
                (14, 2, 1),
                (15, 3, 2),
                (16, 3, 3),
                (17, 2, 3),
                (18, 1, 2),
                (19, 2, 2)
        ),
        resources AS
        (
        SELECT  '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources
        )
SELECT  *
FROM    tiles
JOIN    (
        SELECT  ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
                resource
        FROM    resources
        CROSS JOIN
                LATERAL
                REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
        ) tr
USING   (rn)
rn x y resource
1 0 0 ^
2 1 0 #
3 2 0
4 3 1 #
5 4 2 #
6 4 3 "
7 4 4 "
8 3 4 /
9 2 4 .
10 1 3 "
11 0 2 ^
12 0 1 .
13 1 1 #
14 2 1 ^
15 3 2 /
16 3 3 /
17 2 3 "
18 1 2 /
19 2 2 .

Scores

Each tile (except the desert) should have a score token on it. The scores are from 2 to 12 (those two dice can yield). Score 7 is not used, there are one token each for the scores 2 to 12 and two tokens each for the rest of the scores. This makes 18 tokens in total.

The tricky part is that the rules say the high-score tiles (6 and 8) should not be adjacent to each other. This means that pure random distribution (the one we were using for the resource tiles) would not work here.

The Almanac says: "You may have to swap tokens to ensure that no red numbers are on adjacent hexes". However, the swapping algorithms, unless carefully elaborated, are notorious to introduce bias, so that some layouts might be slightly under-represented while the others are over-represented.

For this reason, if we see a problem with our score layout, we will not try to fix it, and will just generate another one from scratch, until it's good enough.

To do this, we first will generate an array of scores in random order. Then we will unnest it and join with the tiles and resources tables we generated on the previous steps. One tricky part is that we have 19 tiles but 18 scores, so we have to join them skipping the desert tile, so that each score record gets joined with correct tile record. We will work around this with a simple analytic function which skips a number after the desert tile.

Once we have joined tiles and scores, we need to make sure that no 6's or 8's are on adjacent tiles. For this we will make a self-join of the scores and tiles tables, employing some hexagon grid properties.

How do we know two tiles are adjacent? First of all, the have to be within one unit from each other on either axis, meaning that the difference of either coordinate should be from -1 to 1. Second, they (naturally) should not match, that is at least one coordinate should differ between the tiles. Third (and this is how hexagon grids are different from orthogonal grids), while every square on an orthogonal grid has eight neighbors, a hexagon only has six. It means that even if two coordinates are no more than one unit apart, the tiles can still not be neighbors. This is indeed true for tiles (-1, 1) and (1, -1).

So for every tile, the list of its possible neighbors on the grid is (-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0) and (1, 1). The numbers here are of course offsets from the original tile's coordinates. This list is quite concise, so we'll just employ some tuple predicates to make a join condition. We will reject every layout where exist two tiles which are both either 6 or 8, and the differences between the tiles' coordinates make one of the tuples in the list above.

"Rejecting" a layout means we re-shuffle the original scores array and feed it to another iteration of the recursive CTE. Then we rinse and repeat until we get us a good layout.

Here's the recursive query:

SELECT  SETSEED(0.201703);

WITH    RECURSIVE
        resources AS
        (
        SELECT  '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources
        ),
        tiles (rn, x, y) AS
        (
        VALUES
                (1, 0, 0),
                (2, 1, 0),
                (3, 2, 0),
                (4, 3, 1),
                (5, 4, 2),
                (6, 4, 3),
                (7, 4, 4),
                (8, 3, 4),
                (9, 2, 4),
                (10, 1, 3),
                (11, 0, 2),
                (12, 0, 1),
                (13, 1, 1),
                (14, 2, 1),
                (15, 3, 2),
                (16, 3, 3),
                (17, 2, 3),
                (18, 1, 2),
                (19, 2, 2)
        ),
        layout AS
        (
        SELECT  *,
                CASE resource
                WHEN ' ' THEN
                        NULL
                ELSE
                        rn + SUM(CASE resource WHEN ' ' THEN -1 ELSE 0 END) OVER (ORDER BY rn)
                END score_rn
        FROM    tiles
        JOIN    (
                SELECT  ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
                        resource
                FROM    resources
                CROSS JOIN
                        LATERAL
                        REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
                ) tr
        USING   (rn)
        ),
        score AS
        (
        SELECT  1 attempt,
                ARRAY_AGG(s ORDER BY RANDOM()) score_array,
                NULL::BIGINT desert
        FROM    generate_series(2, 12) s
        CROSS JOIN
                generate_series(1, 2) r
        WHERE   s <> 7
                AND NOT (r = 2 AND s IN (2, 12))
        UNION ALL
        SELECT  attempt + 1 attempt,
                sa.score_array,
                (
                SELECT  rn
                FROM    layout
                WHERE   score_rn IS NULL
                ) desert
        FROM    (
                SELECT  *
                FROM    score
                WHERE   EXISTS
                        (
                        SELECT  NULL
                        FROM    (
                                SELECT  *
                                FROM    UNNEST(score_array) WITH ORDINALITY q(s1, score_rn)
                                JOIN    layout
                                USING   (score_rn)
                                ) sc1
                        JOIN    (
                                SELECT  *
                                FROM    UNNEST(score_array) WITH ORDINALITY q(s2, score_rn)
                                JOIN    layout
                                USING   (score_rn)
                                ) sc2
                        ON      s1 IN (6, 8)
                                AND s2 IN (6, 8)
                                AND ((sc1.x - sc2.x), (sc1.y - sc2.y)) IN ((-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0), (1, 1))
                        )
                ) s
        CROSS JOIN
                LATERAL
                (
                SELECT  ARRAY_AGG(score ORDER BY RANDOM()) score_array
                FROM    UNNEST(score_array) WITH ORDINALITY q(score, score_rn)
                ) sa
        ),
        score_good AS
        (
        SELECT  score, score_rn
        FROM    (
                SELECT  *
                FROM    score
                ORDER BY
                        attempt DESC
                LIMIT 1
                ) s
        CROSS JOIN
                LATERAL
                UNNEST(score_array) WITH ORDINALITY q (score, score_rn)
        )
SELECT  *
FROM    score_good
score score_rn
8 1
2 2
6 3
10 4
11 5
11 6
9 7
3 8
4 9
8 10
9 11
4 12
12 13
5 14
10 15
5 16
3 17
6 18

I've added a call to `SETSEED` above so that the results are reproducible.

We can see that it took the query 7 attempts to generate a valid score layout. The first setup had 6 and 8 scores on adjacent tiles 7 and 16; the second one had two 8's in a row on tiles 17 and 18; etc.

Once we have a valid score layout we can join it to the rest of our tables:

SELECT  SETSEED(0.201703);

WITH    RECURSIVE
        resources AS
        (
        SELECT  '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources
        ),
        tiles (rn, x, y) AS
        (
        VALUES
                (1, 0, 0),
                (2, 1, 0),
                (3, 2, 0),
                (4, 3, 1),
                (5, 4, 2),
                (6, 4, 3),
                (7, 4, 4),
                (8, 3, 4),
                (9, 2, 4),
                (10, 1, 3),
                (11, 0, 2),
                (12, 0, 1),
                (13, 1, 1),
                (14, 2, 1),
                (15, 3, 2),
                (16, 3, 3),
                (17, 2, 3),
                (18, 1, 2),
                (19, 2, 2)
        ),
        layout AS
        (
        SELECT  *,
                CASE resource
                WHEN ' ' THEN
                        NULL
                ELSE
                        rn + SUM(CASE resource WHEN ' ' THEN -1 ELSE 0 END) OVER (ORDER BY rn)
                END score_rn
        FROM    tiles
        JOIN    (
                SELECT  ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
                        resource
                FROM    resources
                CROSS JOIN
                        LATERAL
                        REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
                ) tr
        USING   (rn)
        ),
        score AS
        (
        SELECT  1 attempt,
                ARRAY_AGG(s ORDER BY RANDOM()) score_array,
                NULL::BIGINT desert
        FROM    generate_series(2, 12) s
        CROSS JOIN
                generate_series(1, 2) r
        WHERE   s <> 7
                AND NOT (r = 2 AND s IN (2, 12))
        UNION ALL
        SELECT  attempt + 1 attempt,
                sa.score_array,
                (
                SELECT  rn
                FROM    layout
                WHERE   score_rn IS NULL
                ) desert
        FROM    (
                SELECT  *
                FROM    score
                WHERE   EXISTS
                        (
                        SELECT  NULL
                        FROM    (
                                SELECT  *
                                FROM    UNNEST(score_array) WITH ORDINALITY q(s1, score_rn)
                                JOIN    layout
                                USING   (score_rn)
                                ) sc1
                        JOIN    (
                                SELECT  *
                                FROM    UNNEST(score_array) WITH ORDINALITY q(s2, score_rn)
                                JOIN    layout
                                USING   (score_rn)
                                ) sc2
                        ON      s1 IN (6, 8)
                                AND s2 IN (6, 8)
                                AND ((sc1.x - sc2.x), (sc1.y - sc2.y)) IN ((-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0), (1, 1))
                        )
                ) s
        CROSS JOIN
                LATERAL
                (
                SELECT  ARRAY_AGG(score ORDER BY RANDOM()) score_array
                FROM    UNNEST(s.score_array) WITH ORDINALITY q(score, score_rn)
                ) sa
        ),
        score_good AS
        (
        SELECT  score, score_rn, attempt
        FROM    (
                SELECT  *
                FROM    score
                ORDER BY
                        attempt DESC
                LIMIT 1
                ) s
        CROSS JOIN
                LATERAL
                UNNEST(score_array) WITH ORDINALITY q (score, score_rn)
        )
SELECT  *
FROM    layout
LEFT JOIN
        score_good
USING   (score_rn)
ORDER BY
        rn;
score_rn rn x y resource score attempt
1 1 0 0 # 11 5
2 2 1 0 " 11 5
3 3 2 0 . 8 5
4 4 3 1 # 10 5
5 5 4 2 / 8 5
6 6 4 3 " 3 5
7 7 4 4 ^ 3 5
None 8 3 4 None None
8 9 2 4 / 4 5
9 10 1 3 / 5 5
10 11 0 2 # 9 5
11 12 0 1 # 6 5
12 13 1 1 " 9 5
13 14 2 1 ^ 4 5
14 15 3 2 . 2 5
15 16 3 3 ^ 5 5
16 17 2 3 " 10 5
17 18 1 2 / 12 5
18 19 2 2 . 6 5

We have all the data for the resource tiles now.

Harbors

Harbors are pretty straightforward: they are completely random and their positions are fixed. We just need to put them into a string, split the string and randomly re-order the resulting table.

Harbors are technically not placed on hexagons, however, they are exactly there where the hexagons' centers would be if there were hexagons outside the main field. We can encode their positions using the grid coordinates as well. We will also add two numbers for the piers orientations (they will help us with the visualization later):

WITH    RECURSIVE
        resources AS
        (
        SELECT  '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources,
                '/#^' || CHR(34) || '.????'::TEXT harbor_resources
        ),
        harbors (rn, x, y, pier1, pier2) AS
        (
        VALUES
                (1, -1, -1, 0, 1),
                (2, 1, -1, 1, 2),
                (3, 3, 0, 1, 2),
                (4, 5, 2, 2, 3),
                (5, 5, 4, 3, 4),
                (6, 4, 5, 3, 4),
                (7, 2, 5, 4, 5),
                (8, 0, 3, 5, 0),
                (9, -1, 1, 5, 0)
        ),
        harbor_resources AS
        (
        SELECT  '/#>".????'::TEXT harbor_resources
        )
SELECT  resource, rn, x, y, pier1, pier2
FROM    harbors
CROSS JOIN
        resources
JOIN    LATERAL
        (
        SELECT  resource, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn
        FROM    REGEXP_SPLIT_TO_TABLE(harbor_resources, '') q (resource)
        ) q
USING   (rn)
ORDER BY
        RANDOM()
resource rn x y pier1 pier2
# 3 3 0 1 2
" 5 5 4 3 4
? 1 -1 -1 0 1
? 8 0 3 5 0
^ 4 5 2 2 3
/ 9 -1 1 5 0
? 7 2 5 4 5
? 6 4 5 3 4
. 2 1 -1 1 2

Putting it all together

Right now we have all information we need to run the game. However, it would help to visualize the layout.

We'll draw the hexagons using different characters for different types of resources, and place the score token inside each hexagon. We will also put a smaller empty hexagon inside each large one, so that the score token would be clearly visible. Finally, we will put the harbors and their piers on the map.

To do that, we will need to generate character maps for every piece of information we are about to put on the screen.

For the hexagons we will first generate a square using a cross-join of two GENERATE_SERIES then filter it so as to make it a hexagon.

The centers of each hexagons are calculated from their x and y coordinates: x is taken as is; y is shifted by half the respective x value (remember that our axes are at 60° to each other).

The filtering condition for the hexagon is this: if the character is within -1/4 to +1/4 of height, starting from the center, we output it without further ado; if it's between -1/2 to -1/4 (or 1/4 to 1/2 on the other side), we only output it if its horizontal coordinate is no more than twice the distance from the top or bottom, respectively. The first part forms a rectangle inside the hexagon; the second one forms two triangles on the top and on the bottom.

The smaller hexagons are generated using the same formula, only their total height and width are smaller.

The score is printed inside each hexagon's center. We can't just output it as is the way we do with the resource characters, because some of the scores are two-digit. To deal with this, we should break each score into separate characters and put each character into its own place on the screen.

We also need to do harbors and piers. The harbor resource characters are just placed at the coordinates on the grid (with the above formulas in mind). The piers are shifted by approximate values of sines and cosines of their respective angles (encoded as 60° factors). We do not calculate the sines and cosines directly, and rather use approximation tables.

Some of the resulting characters will overlap: for instance, the center of each tile will contain the character from the larger hexagon, the smaller hexagon and the resource. To work around this, we will assign the layer number to each character. The larger hexagons are on the layer 1, the smaller ones on the layer 2, and, finally, the score character are on the topmost layer 3. If several characters have the same coordinates, the one with the highest layer number wins. We will be doing all of them in a single query, that's why the harbors and piers will need to have the layers too, even if they don't overlap with anything. We will assign them the layer 4.

Once the character maps are ready, the rest is business as usual: we generate the field by cross joining two GENERATE_SERIES together, left join the result with the character maps and replace the NULL values from the left join with spaces. Then we group the characters by rows and output the rows in order.

Here's what we got:

SELECT  SETSEED(0.201704);

WITH    RECURSIVE
        resources AS
        (
        SELECT  '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources,
                '/#^' || CHR(34) || '.????'::TEXT harbor_resources
        ),
        tiles (rn, x, y) AS
        (
        VALUES
                (1, 0, 0),
                (2, 1, 0),
                (3, 2, 0),
                (4, 3, 1),
                (5, 4, 2),
                (6, 4, 3),
                (7, 4, 4),
                (8, 3, 4),
                (9, 2, 4),
                (10, 1, 3),
                (11, 0, 2),
                (12, 0, 1),
                (13, 1, 1),
                (14, 2, 1),
                (15, 3, 2),
                (16, 3, 3),
                (17, 2, 3),
                (18, 1, 2),
                (19, 2, 2)
        ),
        harbors (rn, x, y, pier1, pier2) AS
        (
        VALUES
                (1, -1, -1, 0, 1),
                (2, 1, -1, 1, 2),
                (3, 3, 0, 1, 2),
                (4, 5, 2, 2, 3),
                (5, 5, 4, 3, 4),
                (6, 4, 5, 3, 4),
                (7, 2, 5, 4, 5),
                (8, 0, 3, 5, 0),
                (9, -1, 1, 5, 0)
        ),
        score AS
        (
        SELECT  1 attempt,
                ARRAY_AGG(s ORDER BY RANDOM()) score_array
        FROM    generate_series(2, 12) s
        CROSS JOIN
                generate_series(1, 2) r
        WHERE   s <> 7
                AND NOT (r = 2 AND s IN (2, 12))
        UNION ALL
        SELECT  attempt + 1 attempt,
                sa.score_array
        FROM    (
                SELECT  *
                FROM    score
                WHERE   EXISTS
                        (
                        SELECT  NULL
                        FROM    (
                                SELECT  *
                                FROM    UNNEST(score_array) WITH ORDINALITY q(s1, rn)
                                JOIN    tiles
                                USING   (rn)
                                ) sc1
                        JOIN    (
                                SELECT  *
                                FROM    UNNEST(score_array) WITH ORDINALITY q(s2, rn)
                                JOIN    tiles t
                                USING   (rn)
                                ) sc2
                        ON      s1 IN (6, 8)
                                AND s2 IN (6, 8)
                                AND ((sc1.x - sc2.x), (sc1.y - sc2.y)) IN ((-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0), (1, 1))
                        )
                ) s
        CROSS JOIN
                LATERAL
                (
                SELECT  ARRAY_AGG(score ORDER BY RANDOM()) score_array
                FROM    UNNEST(score_array) WITH ORDINALITY q(score, score_rn)
                ) sa
        ),
        score_good AS
        (
        SELECT  score, score_rn
        FROM    (
                SELECT  *
                FROM    score
                ORDER BY
                        attempt DESC
                LIMIT 1
                ) s
        CROSS JOIN
                LATERAL
                UNNEST(score_array) WITH ORDINALITY q (score, score_rn)
        ),
        layout AS
        (
        SELECT  *
        FROM    (
                SELECT  *,
                        CASE resource
                        WHEN ' ' THEN
                                NULL
                        ELSE
                                rn + SUM(CASE resource WHEN ' ' THEN -1 ELSE 0 END) OVER (ORDER BY rn)
                        END score_rn
                FROM    tiles
                JOIN    (
                        SELECT  ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
                                resource
                        FROM    resources
                        CROSS JOIN
                                LATERAL
                                REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
                        ) tr
                USING   (rn)
                ) t
        LEFT JOIN
                score_good
        USING   (score_rn)
        ORDER BY
                rn
        )
SELECT  row
FROM    (
        SELECT  r,
                STRING_AGG(COALESCE(letter, ' '), '' ORDER BY c) AS row
        FROM    generate_series(0, 70) r
        CROSS JOIN
                generate_series(0, 89) c
        LEFT JOIN
                (
                SELECT  *
                FROM    (
                        SELECT  *,
                                ROW_NUMBER() OVER (PARTITION BY r, c ORDER BY layer DESC) rn
                        FROM    (
                                SELECT  10 height,
                                        16 width
                                ) d
                        CROSS JOIN
                                LATERAL
                                (
                                SELECT  letter, r, c, layer
                                FROM    layout
                                CROSS JOIN
                                        LATERAL
                                        (
                                        SELECT  height * x + 15 center_r,
                                                width * y - (width / 2)::INT * x + 24 center_c
                                        ) c
                                CROSS JOIN
                                        LATERAL
                                        (
                                        SELECT  *
                                        FROM    (
                                                SELECT  1 layer, resource letter, center_r + rs r, center_c + cs c
                                                FROM    (
                                                        SELECT  height * 1.5 * 0.8 th, width * 0.9 tw
                                                        ) t
                                                CROSS JOIN
                                                        generate_series(-(th / 2)::INT, (th / 2)::INT) rs
                                                CROSS JOIN
                                                        generate_series(-(tw / 2)::INT, (tw / 2)::INT ) cs
                                                CROSS JOIN
                                                        LATERAL
                                                        (
                                                        SELECT  rs::FLOAT / th rsf, cs::FLOAT / tw csf
                                                        ) f
                                                WHERE   rsf BETWEEN -0.25 AND 0.25
                                                        OR
                                                        ABS(csf) BETWEEN 0 AND 1 - ABS(rsf * 2)
                                                UNION ALL
                                                SELECT  2 layer, ' ', center_r + rs r, center_c + cs c
                                                FROM    (
                                                        SELECT  height * 1.5 * 0.35 th, width * 0.35 tw
                                                        ) t
                                                CROSS JOIN
                                                        generate_series(-(th / 2)::INT, (th / 2)::INT) rs
                                                CROSS JOIN
                                                        generate_series(-(tw / 2)::INT, (tw / 2)::INT ) cs
                                                CROSS JOIN
                                                        LATERAL
                                                        (
                                                        SELECT  rs::FLOAT / th rsf, cs::FLOAT / tw csf
                                                        ) f
                                                WHERE   rsf BETWEEN -0.25 AND 0.25
                                                        OR
                                                        ABS(csf) BETWEEN 0 AND 1 - ABS(rsf * 2)
                                                UNION ALL
                                                SELECT  3 layer, score_letter letter, center_r r, center_c + pos - 1 c
                                                FROM    REGEXP_SPLIT_TO_TABLE(score::TEXT, '') WITH ORDINALITY l(score_letter, pos)
                                                ) q
                                        ) q2
                                UNION ALL
                                SELECT  letter, r, c, 4 layer
                                FROM    harbors
                                JOIN    LATERAL
                                        (
                                        SELECT  resource, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn
                                        FROM    resources
                                        CROSS JOIN
                                                LATERAL
                                                REGEXP_SPLIT_TO_TABLE(harbor_resources, '') q (resource)
                                        ) q2
                                USING   (rn)
                                CROSS JOIN
                                        LATERAL
                                        (
                                        SELECT  height * x + 15 center_r,
                                                width * y - (width / 2)::INT * x + 25 center_c
                                        ) c
                                CROSS JOIN
                                        LATERAL
                                        (
                                        SELECT  resource letter, center_r r, center_c c
                                        UNION ALL
                                        SELECT  letter, r, c
                                        FROM    (
                                                SELECT  pier1
                                                UNION ALL
                                                SELECT  pier2
                                                ) p (pier)
                                        CROSS JOIN
                                                LATERAL
                                                (
                                                SELECT  SUBSTRING('|\/|\/', (pier + 1), 1) letter,
                                                        center_r + ((ARRAY[0.4, 0.2, -0.2, -0.4, -0.2, 0.2])[pier + 1] * height * 1.5 * 0.8)::INT r,
                                                        center_c + ((ARRAY[0, 0.3, 0.3, 0, -0.3, -0.3])[pier + 1] * width * 0.9)::INT c
                                                ) pl
                                        ) p2
                                ) q3
                        ) l
                        WHERE   rn = 1
                ) t
        USING   (r, c)
        GROUP BY
                r
        ) q
ORDER BY
        r
row
. ^
\ /
/ . "
| ///// ..... | """""
///////// ......... """""""""
/////////////// ............... """""""""""""""
////// ////// ...... ...... """""" """"""
//// //// .... .... """" """"
//// 5 //// .... 11 .... """" 8 """" "
//// //// .... .... """" """"
////// ////// ...... ...... """""" """""" /
/////////////// ............... """""""""""""""
" ///////// " ......... ^ """"""""" "
""""" ///// """"" ..... ^^^^^ """"" """"" |
""""""""" / """"""""" . ^^^^^^^^^ " """""""""
""""""""""""""" """"""""""""""" ^^^^^^^^^^^^^^^ """""""""""""""
/ """""" """""" """""" """""" ^^^^^^ ^^^^^^ """""" """"""
"""" """" """" """" ^^^^ ^^^^ """" """"
? """" 9 """" """" 5 """" ^^^^ 6 ^^^^ """" 4 """"
"""" """" """" """" ^^^^ ^^^^ """" """"
\ """""" """""" """""" """""" ^^^^^^ ^^^^^^ """""" """"""
""""""""""""""" """"""""""""""" ^^^^^^^^^^^^^^^ """""""""""""""
^ """"""""" """"""""" / ^^^^^^^^^ . """"""""" #
^^^^^ """"" """"" ///// ^^^^^ ..... """"" #####
^^^^^^^^^ " " ///////// ^ ......... " #########
^^^^^^^^^^^^^^^ /////////////// ............... ###############
^^^^^^ ^^^^^^ ////// ////// ...... ...... ###### ###### \
^^^^ ^^^^ //// //// .... .... #### ####
^^^^ 11 ^^^^ //// 3 //// .... 9 .... #### 12 #### ?
^^^^ ^^^^ //// //// .... .... #### ####
^^^^^^ ^^^^^^ ////// ////// ...... ...... ###### ###### /
^^^^^^^^^^^^^^^ /////////////// ............... ###############
^^^^^^^^^ # / ///////// # ......... # #########
^^^^^ ##### ///// ///// ##### ..... ##### #####
^ ######### ///////// / ######### . ######### #
############### /////////////// ############### ###############
/ ###### ###### ////// ////// ###### ###### ###### ######
#### #### //// //// #### #### #### ####
/ #### 6 #### //// 10 //// #### 4 #### #### 2 ####
#### #### //// //// #### #### #### ####
\ ###### ###### ////// ////// ###### ###### ###### ######
############### /////////////// ############### ###############
######### / ///////// . ######### ^ #########
##### ///// ///// ..... ##### ^^^^^ ##### |
# ///////// / ......... # ^^^^^^^^^ #
/////////////// ............... ^^^^^^^^^^^^^^^
////// ////// ...... ...... ^^^^^^ ^^^^^^ \
//// //// .... .... ^^^^ ^^^^
//// 10 //// .... 8 .... ^^^^ 3 ^^^^ #
//// //// .... .... ^^^^ ^^^^
////// ////// ...... ...... ^^^^^^ ^^^^^^
/////////////// ............... ^^^^^^^^^^^^^^^
///////// ......... ^^^^^^^^^
| ///// ..... | ^^^^^
/ . ^
/ \
? ?

You can view the queries here: https://github.com/quassnoi/explain-extended-2018

Let this New Year bring you the joy of playing with your friends and family!

Happy New Year!

Previous New Year posts:

Written by Quassnoi

December 31st, 2017 at 11:00 pm

Posted in PostgreSQL

Leave a Reply