hasura.md

Tables + function SQL

Function:

CREATE
OR REPLACE FUNCTION public.random_media(n integer) RETURNS SETOF media LANGUAGE sql STABLE AS $ function $
select
  *
from
  media TABLESAMPLE SYSTEM_ROWS(n) $ function $

Tables:

CREATE TABLE public.media (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    filename text NOT NULL,
    link text NOT NULL
);

CREATE TABLE public.media_tags (
    tag text NOT NULL,
    media uuid NOT NULL
);

CREATE TABLE public.tags (
    name text NOT NULL
);


ALTER TABLE ONLY public.media
    ADD CONSTRAINT media_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.media_tags
    ADD CONSTRAINT media_tags_pkey PRIMARY KEY (tag, media);
ALTER TABLE ONLY public.tags
    ADD CONSTRAINT tags_pkey PRIMARY KEY (name);
ALTER TABLE ONLY public.media_tags
    ADD CONSTRAINT media_tags_media_fkey FOREIGN KEY (media) REFERENCES public.media(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.media_tags
    ADD CONSTRAINT media_tags_tag_fkey FOREIGN KEY (tag) REFERENCES public.tags(name) ON UPDATE RESTRICT ON DELETE RESTRICT;

Hasura

GraphQL query:

query MyQuery {
  random_media(args: {n: 10}) {
    filename
    tags {
      tagByTag {
        name
      }
    }
  }
}

SQL:

SELECT
  coalesce(json_agg("root"), '[]') AS "root"
FROM
  (
    SELECT
      row_to_json(
        (
          SELECT
            "_9_e"
          FROM
            (
              SELECT
                "_1_root.base"."filename" AS "filename",
                "_8_root.ar.root.tags"."tags" AS "tags"
            ) AS "_9_e"
        )
      ) AS "root"
    FROM
      (
        SELECT
          *
        FROM
          "public"."random_media"(('10') :: integer) AS "_0_random_media"
        WHERE
          ('true')
      ) AS "_1_root.base"
      LEFT OUTER JOIN LATERAL (
        SELECT
          coalesce(json_agg("tags"), '[]') AS "tags"
        FROM
          (
            SELECT
              row_to_json(
                (
                  SELECT
                    "_6_e"
                  FROM
                    (
                      SELECT
                        "_5_root.ar.root.tags.or.tagByTag"."tagByTag" AS "tagByTag"
                    ) AS "_6_e"
                )
              ) AS "tags"
            FROM
              (
                SELECT
                  *
                FROM
                  "public"."media_tags"
                WHERE
                  (("_1_root.base"."id") = ("media"))
              ) AS "_2_root.ar.root.tags.base"
              LEFT OUTER JOIN LATERAL (
                SELECT
                  row_to_json(
                    (
                      SELECT
                        "_4_e"
                      FROM
                        (
                          SELECT
                            "_3_root.ar.root.tags.or.tagByTag.base"."name" AS "name"
                        ) AS "_4_e"
                    )
                  ) AS "tagByTag"
                FROM
                  (
                    SELECT
                      *
                    FROM
                      "public"."tags"
                    WHERE
                      (("_2_root.ar.root.tags.base"."tag") = ("name"))
                  ) AS "_3_root.ar.root.tags.or.tagByTag.base"
              ) AS "_5_root.ar.root.tags.or.tagByTag" ON ('true')
          ) AS "_7_root.ar.root.tags"
      ) AS "_8_root.ar.root.tags" ON ('true')
  ) AS "_10_root"

EXPLAIN ANALYZE:

                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=43.33..43.34 rows=1 width=32) (actual time=0.156..0.157 rows=1 loops=1)
   ->  Nested Loop Left Join  (cost=3.88..43.17 rows=10 width=62) (actual time=0.029..0.145 rows=10 loops=1)
         ->  Sample Scan on media  (cost=0.00..4.10 rows=10 width=46) (actual time=0.006..0.007 rows=10 loops=1)
               Sampling: system_rows ('10'::bigint)
         ->  Aggregate  (cost=3.88..3.89 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=10)
               ->  Nested Loop Left Join  (cost=0.00..3.86 rows=1 width=32) (actual time=0.006..0.012 rows=1 loops=10)
                     Join Filter: (media_tags.tag = tags.name)
                     ->  Seq Scan on media_tags  (cost=0.00..2.83 rows=1 width=5) (actual time=0.004..0.010 rows=1 loops=10)
                           Filter: (media.id = media)
                           Rows Removed by Filter: 145
                     ->  Seq Scan on tags  (cost=0.00..1.02 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=10)
                           SubPlan 2
                             ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=10)
               SubPlan 3
                 ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=10)
   SubPlan 1
     ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=10)
 Planning Time: 0.219 ms
 Execution Time: 0.178 ms
(19 rows)

Hand-written SQL:

SELECT filename, tag FROM random_media(10) AS m INNER JOIN media_tags ON media = m.id;

EXPLAIN ANALYZE:

                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4.22..7.08 rows=146 width=35) (actual time=0.034..0.038 rows=10 loops=1)
   Hash Cond: (media_tags.media = media.id)
   ->  Seq Scan on media_tags  (cost=0.00..2.46 rows=146 width=21) (actual time=0.005..0.012 rows=146 loops=1)
   ->  Hash  (cost=4.10..4.10 rows=10 width=46) (actual time=0.008..0.008 rows=10 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Sample Scan on media  (cost=0.00..4.10 rows=10 width=46) (actual time=0.004..0.006 rows=10 loops=1)
               Sampling: system_rows ('10'::bigint)
 Planning Time: 0.114 ms
 Execution Time: 0.048 ms
(9 rows)