sql: TPC-H query 8 exceeds memory budget

[asubiotto]

$ ./tpch -dist-sql=false -queries 8 -v
Starting TPC-H load generator
Setting up the database. Connecting to db: postgresql://root@localhost:26257/tpch?sslmode=disable
Error occured when running query 8: pq: sql: memory budget exceeded: 10240 bytes requested, 4294967296 bytes in budget
Finished query 8: 0 rows returned after 26.36 seconds

This is using single-node sql running 61ed30f. Here is the query:

SELECT
    o_year,
    SUM(CASE
        WHEN nation = 'CANADA' THEN volume
        ELSE 0
    END) / SUM(volume) AS mkt_share
FROM
    (
        SELECT
            EXTRACT(year FROM o_orderdate) AS o_year,
            l_extendedprice * (1 - l_discount) AS volume,
            n2.n_name AS nation
        FROM
            part,
            supplier,
            lineitem,
            orders,
            customer,
            nation n1,
            nation n2,
            region
        WHERE
            p_partkey = l_partkey
            AND s_suppkey = l_suppkey
            AND l_orderkey = o_orderkey
            AND o_custkey = c_custkey
            AND c_nationkey = n1.n_nationkey
            AND n1.n_regionkey = r_regionkey
            AND r_name = 'AMERICA'
            AND s_nationkey = n2.n_nationkey
            AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
            AND p_type = 'ECONOMY POLISHED STEEL'
    ) AS all_nations
GROUP BY
    o_year
ORDER BY
    o_year;

+-------+------------+----------+-----------------------------------------------------+
| Level |    Type    |  Field   |                     Description                     |
+-------+------------+----------+-----------------------------------------------------+
|     0 | sort       |          |                                                     |
|     0 |            | order    | +o_year                                             |
|     1 | group      |          |                                                     |
|     2 | render     |          |                                                     |
|     3 | render     |          |                                                     |
|     4 | join       |          |                                                     |
|     4 |            | type     | inner                                               |
|     4 |            | equality | (p_partkey) = (l_partkey)                           |
|     5 | scan       |          |                                                     |
|     5 |            | table    | part@primary                                        |
|     5 |            | spans    | ALL                                                 |
|     5 | join       |          |                                                     |
|     5 |            | type     | inner                                               |
|     5 |            | equality | (s_suppkey, s_nationkey) = (l_suppkey, n_nationkey) |
|     6 | scan       |          |                                                     |
|     6 |            | table    | supplier@primary                                    |
|     6 |            | spans    | ALL                                                 |
|     6 | join       |          |                                                     |
|     6 |            | type     | inner                                               |
|     6 |            | equality | (l_orderkey) = (o_orderkey)                         |
|     7 | scan       |          |                                                     |
|     7 |            | table    | lineitem@primary                                    |
|     7 |            | spans    | ALL                                                 |
|     7 | join       |          |                                                     |
|     7 |            | type     | inner                                               |
|     7 |            | equality | (o_custkey) = (c_custkey)                           |
|     8 | index-join |          |                                                     |
|     9 | scan       |          |                                                     |
|     9 |            | table    | orders@o_od                                         |
|     9 |            | spans    | /9131-/9862                                         |
|     9 | scan       |          |                                                     |
|     9 |            | table    | orders@primary                                      |
|     8 | join       |          |                                                     |
|     8 |            | type     | inner                                               |
|     8 |            | equality | (c_nationkey) = (n_nationkey)                       |
|     9 | scan       |          |                                                     |
|     9 |            | table    | customer@primary                                    |
|     9 |            | spans    | ALL                                                 |
|     9 | join       |          |                                                     |
|     9 |            | type     | inner                                               |
|     9 |            | equality | (n_regionkey) = (r_regionkey)                       |
|    10 | scan       |          |                                                     |
|    10 |            | table    | nation@primary                                      |
|    10 |            | spans    | ALL                                                 |
|    10 | join       |          |                                                     |
|    10 |            | type     | cross                                               |
|    11 | scan       |          |                                                     |
|    11 |            | table    | nation@primary                                      |
|    11 |            | spans    | ALL                                                 |
|    11 | scan       |          |                                                     |
|    11 |            | table    | region@primary                                      |
|    11 |            | spans    | ALL                                                 |
+-------+------------+----------+-----------------------------------------------------+

Looking at a memory profile while the query is running shows nothing surprising apart from the amount of memory the budget says we have exceeded in contrast with what is shown in the profile, note however that I do download the profile while the query is running (but did so a second or so before the error is reported) so this might be showing an early enough sample.

The interesting thing here is that @arjunravinarayan says that TPC-H query 8 used to run fine so we have to figure out what changed.

[andreimatei]

It wouldn't be the first time we "leak" accounted memory.

[asubiotto]

Looking at memory usage through the admin ui confirms that memory is accounted for correctly. I check out a commit from March 1st (2817772) which I believe was around when these TPC-H queries were run with single-node SQL and ran into the same issue.

This query runs but uses up ~13.8GiB without memory accounting. I don't think there's anything else left to do here as this pertains to limiting large memory usage by queries (see #15206). Closing this issue.