Hasura query is slower compared to the equivalent handwritten SQL query

[SidharthBihary]

Hasura query(both the graphql query and the hasura generated SQL query when executed directly in DB) takes around 700ms where as the equivalent SQL query(which I wrote) takes around 30ms to execute. These times are the DB execution times in PostgresDB. The response is of descent size having >14k rows.
Relevant details are below.

1. my use case: There are 3 tables in the DB, namely authors, articles and author-article. There is many-to-many relationship between authors and articles tables using author-article table. My query is to find out all the articles written by authors having author_id <= 485(some number) where article_id should be greater than 100(again some number).
2. hasura graphql query is: [DB execution time is ~700ms]

query myQuery {
  authors(where: {id: {_lte: 485}}) {
    id
    author_name
    articles(where: {article_id: {_gt: 100}}) {
      articles {
        article_title
      }
    }
  }
}

3. equivalent SQL query written by me is: [DB execution time is ~30ms]

select *
	from 
	(
		(
    		select * from authors where authors.id <= 485
		) as au
		inner join
		(
    		select * from author_article
		) as ar on au.id = ar.author_id
	) as x
	inner join 
	articles on x.article_id = articles.id and articles.id > 100;

4. hasura generated SQL query is: [DB execution time is ~700ms]

SELECT
  coalesce(json_agg("root"), '[]') AS "root"
FROM
  (
    SELECT
      row_to_json(
        (
          SELECT
            "_8_e"
          FROM
            (
              SELECT
                "_0_root.base"."id" AS "id",
                "_0_root.base"."author_name" AS "author_name",
                "_7_root.ar.root.articles"."articles" AS "articles"
            ) AS "_8_e"
        )
      ) AS "root"
    FROM
      (
        SELECT
          *
        FROM
          "public"."authors"
        WHERE
          (("public"."authors"."id") <= (('485') :: integer))
      ) AS "_0_root.base"
      LEFT OUTER JOIN LATERAL (
        SELECT
          coalesce(json_agg("articles"), '[]') AS "articles"
        FROM
          (
            SELECT
              row_to_json(
                (
                  SELECT
                    "_5_e"
                  FROM
                    (
                      SELECT
                        "_4_root.ar.root.articles.or.articles"."articles" AS "articles"
                    ) AS "_5_e"
                )
              ) AS "articles"
            FROM
              (
                SELECT
                  *
                FROM
                  "public"."author_article"
                WHERE
                  (
                    (("_0_root.base"."id") = ("author_id"))
                    AND (
                      ("public"."author_article"."article_id") > (('100') :: integer)
                    )
                  )
              ) AS "_1_root.ar.root.articles.base"
              LEFT OUTER JOIN LATERAL (
                SELECT
                  row_to_json(
                    (
                      SELECT
                        "_3_e"
                      FROM
                        (
                          SELECT
                            "_2_root.ar.root.articles.or.articles.base"."article_title" AS "article_title"
                        ) AS "_3_e"
                    )
                  ) AS "articles"
                FROM
                  (
                    SELECT
                      *
                    FROM
                      "public"."articles"
                    WHERE
                      (
                        ("_1_root.ar.root.articles.base"."article_id") = ("id")
                      )
                  ) AS "_2_root.ar.root.articles.or.articles.base"
              ) AS "_4_root.ar.root.articles.or.articles" ON ('true')
          ) AS "_6_root.ar.root.articles"
      ) AS "_7_root.ar.root.articles" ON ('true')
  ) AS "_9_root"

When I run the hasura generated SQL query directly in the DB, still the DB execution time is high(~700ms) compared to my SQL query(~30ms).

I am also attaching the query plans(with and without ANALYZE) for hasura SQL and my SQL.

5. EXPLAIN <hasura generated SQL query>

Aggregate  (cost=225314.57..225314.58 rows=1 width=32)
  ->  Nested Loop Left Join  (cost=464.50..225307.30 rows=485 width=46)
        ->  Seq Scan on authors  (cost=0.00..9.25 rows=485 width=14)
              Filter: (id <= 485)
        ->  Aggregate  (cost=464.50..464.51 rows=1 width=32)
              ->  Nested Loop Left Join  (cost=0.28..464.06 rows=29 width=32)
                    ->  Seq Scan on author_article  (cost=0.00..307.00 rows=29 width=4)
                          Filter: ((article_id > 100) AND (authors.id = author_id))
                    ->  Index Scan using articles_pkey on articles  (cost=0.28..5.42 rows=1 width=36)
                          Index Cond: (author_article.article_id = id)
                          SubPlan 2
                            ->  Result  (cost=0.00..0.01 rows=1 width=32)
              SubPlan 3
                ->  Result  (cost=0.00..0.01 rows=1 width=32)
  SubPlan 1
    ->  Result  (cost=0.00..0.01 rows=1 width=32)

6. EXPLAIN ANALYZE <hasura generated SQL query>

Aggregate  (cost=225314.57..225314.58 rows=1 width=32) (actual time=638.218..638.218 rows=1 loops=1)
  ->  Nested Loop Left Join  (cost=464.50..225307.30 rows=485 width=46) (actual time=1.952..634.292 rows=485 loops=1)
        ->  Seq Scan on authors  (cost=0.00..9.25 rows=485 width=14) (actual time=0.020..0.144 rows=485 loops=1)
              Filter: (id <= 485)
              Rows Removed by Filter: 15
        ->  Aggregate  (cost=464.50..464.51 rows=1 width=32) (actual time=1.307..1.307 rows=1 loops=485)
              ->  Nested Loop Left Join  (cost=0.28..464.06 rows=29 width=32) (actual time=0.053..1.279 rows=29 loops=485)
                    ->  Seq Scan on author_article  (cost=0.00..307.00 rows=29 width=4) (actual time=0.041..1.186 rows=29 loops=485)
                          Filter: ((article_id > 100) AND (authors.id = author_id))
                          Rows Removed by Filter: 14971
                    ->  Index Scan using articles_pkey on articles  (cost=0.28..5.42 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=14272)
                          Index Cond: (author_article.article_id = id)
                          SubPlan 2
                            ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=14272)
              SubPlan 3
                ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=14272)
  SubPlan 1
    ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=485)
Planning Time: 52.788 ms
Execution Time: 638.583 ms

7. EXPLAIN <my SQL query>

Hash Join  (cost=175.06..485.00 rows=14259 width=50)
  Hash Cond: (author_article.article_id = articles.id)
  ->  Hash Join  (cost=15.31..287.02 rows=14550 width=26)
        Hash Cond: (author_article.author_id = authors.id)
        ->  Seq Scan on author_article  (cost=0.00..232.00 rows=15000 width=12)
        ->  Hash  (cost=9.25..9.25 rows=485 width=14)
              ->  Seq Scan on authors  (cost=0.00..9.25 rows=485 width=14)
                    Filter: (id <= 485)
  ->  Hash  (cost=98.50..98.50 rows=4900 width=24)
        ->  Seq Scan on articles  (cost=0.00..98.50 rows=4900 width=24)
              Filter: (id > 100)

8. EXPLAIN ANALYZE <my SQL query>

Hash Join  (cost=175.06..485.00 rows=14259 width=50) (actual time=1.871..10.793 rows=14272 loops=1)
  Hash Cond: (author_article.article_id = articles.id)
  ->  Hash Join  (cost=15.31..287.02 rows=14550 width=26) (actual time=0.184..5.238 rows=14576 loops=1)
        Hash Cond: (author_article.author_id = authors.id)
        ->  Seq Scan on author_article  (cost=0.00..232.00 rows=15000 width=12) (actual time=0.021..1.401 rows=15000 loops=1)
        ->  Hash  (cost=9.25..9.25 rows=485 width=14) (actual time=0.154..0.154 rows=485 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 31kB
              ->  Seq Scan on authors  (cost=0.00..9.25 rows=485 width=14) (actual time=0.016..0.087 rows=485 loops=1)
                    Filter: (id <= 485)
                    Rows Removed by Filter: 15
  ->  Hash  (cost=98.50..98.50 rows=4900 width=24) (actual time=1.649..1.649 rows=4900 loops=1)
        Buckets: 8192  Batches: 1  Memory Usage: 348kB
        ->  Seq Scan on articles  (cost=0.00..98.50 rows=4900 width=24) (actual time=0.017..0.883 rows=4900 loops=1)
              Filter: (id > 100)
              Rows Removed by Filter: 100
Planning Time: 0.351 ms
Execution Time: 11.276 ms

[clausMeko]

#6448

[vijaya-lakshmi-venkatraman]

HI
Is there an update on this issue?

[JCatrielLopez]

bump