0

Have the following function:

CREATE FUNCTION Game.operator_quotes(i_operator_name TEXT, i_locale_id SMALLINT)
    RETURNS TABLE (
        name TEXT,
        rarity SMALLINT,
        quotes JSONB
    )
    AS
    $$
    BEGIN
    RETURN QUERY
        SELECT 
            op_tl.name,
            op_data.rarity,
            op_quotes.quotes

        FROM
            Game.OperatorTranslation op_tl

        JOIN (SELECT op.operator_id, op.rarity FROM Game.Operator op) op_data ON op_data.operator_id = op_tl.operator_id

        JOIN (
            SELECT 
                op_quotes.operator_id, 
                op_quotes.locale_id, 
                jsonb_agg(jsonb_build_object('key', quote_key.key, 'quote', op_quotes.quote) ORDER BY quote_key.key_id) AS "quotes"

            FROM Game.OperatorQuoteTranslation op_quotes

            JOIN (
                SELECT quote_key.key_id, quote_key.key, quote_key.locale_id 
                FROM Game.QuoteKeyTranslation quote_key
            ) quote_key ON quote_key.key_id = op_quotes.key_id AND quote_key.locale_id = i_locale_id

            GROUP by op_quotes.locale_id, op_quotes.operator_id

        ) op_quotes ON op_quotes.operator_id = op_tl.operator_id AND op_quotes.locale_id = i_locale_id

        WHERE op_tl.name ~* i_operator_name;
        END
        $$
        LANGUAGE plpgsql;

Which running as a standalone query (I'm not aware on how to get the query plan for a query within a function) I get the following plan:

                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=541.60..615.72 rows=2 width=44) (actual time=44.874..48.275 rows=1 loops=1)
   Join Filter: (op_tl.operator_id = op.operator_id)
   ->  Nested Loop  (cost=541.46..615.31 rows=2 width=50) (actual time=44.840..48.240 rows=1 loops=1)
         Join Filter: (op_tl.operator_id = op_quotes.operator_id)
         Rows Removed by Join Filter: 88
         ->  GroupAggregate  (cost=541.46..585.36 rows=484 width=38) (actual time=7.270..47.701 rows=89 loops=1)
               Group Key: op_quotes.locale_id, op_quotes.operator_id
               ->  Sort  (cost=541.46..549.03 rows=3028 width=98) (actual time=6.937..13.781 rows=3028 loops=1)
                     Sort Key: op_quotes.operator_id
                     Sort Method: quicksort  Memory: 593kB
                     ->  Hash Join  (cost=3.19..366.38 rows=3028 width=98) (actual time=0.074..5.378 rows=3028 loops=1)
                           Hash Cond: (op_quotes.key_id = quote_key.key_id)
                           ->  Seq Scan on operatorquotetranslation op_quotes  (cost=0.00..354.27 rows=3028 width=84) (actual time=0.017..3.976 rows=3028 loops=1)
                                 Filter: (locale_id = 3)
                                 Rows Removed by Filter: 10194
                           ->  Hash  (cost=2.75..2.75 rows=35 width=16) (actual time=0.047..0.048 rows=35 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                 ->  Seq Scan on quotekeytranslation quote_key  (cost=0.00..2.75 rows=35 width=16) (actual time=0.009..0.033 rows=35 loops=1)
                                       Filter: (locale_id = 3)
                                       Rows Removed by Filter: 105
         ->  Materialize  (cost=0.00..17.86 rows=1 width=14) (actual time=0.001..0.005 rows=1 loops=89)
               ->  Seq Scan on operatortranslation op_tl  (cost=0.00..17.85 rows=1 width=14) (actual time=0.045..0.426 rows=1 loops=1)
                     Filter: (name ~* 'Skadi'::text)
                     Rows Removed by Filter: 387
   ->  Index Scan using operator_pkey on operator op  (cost=0.14..0.19 rows=1 width=6) (actual time=0.026..0.027 rows=1 loops=1)
         Index Cond: (operator_id = op_quotes.operator_id)
 Planning Time: 0.939 ms
 Execution Time: 48.373 ms
(28 rows)

Clearly the issue here is the GroupAggregate which makes for most if not all the execution time of the query, I assume here it has to scan the entire table and I'm sure I can seek for some performance improvements here. So I thought of two solutions where both are similar but I'm not too complacent with either of them.

Attempt(s)

Both attempts are grabbing the value of "operator_id" which is later JOIN(ed) ON and also use it to filter on the JOIN clause before the GROUP BY is applied. Except that both differ on the way of getting said value, one would be using a separate query and then pass it as an argument to this function and adding a WHERE clause on the JOIN, the issue I came up with this is that I would be effectively SELECT(ing) the Game.OperatorTranslation table twice. The second attempt was to, in this same function, query the Game.OperatorTranslation table but store it in a variable and then using the operator_id value of this table to filter within the JOIN (assuming this one is possible)

I can attest that the first one gives the performance improvement but in exchange for having to use two queries.

Is there any nicer/better approach for this?

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.