Hasura query is slower compared to the equivalent handwritten SQL query #7079
Description
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.
- my use case: There are 3 tables in the DB, namely
authors,articlesandauthor-article. There is many-to-many relationship betweenauthorsandarticlestables usingauthor-articletable. 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). - 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
}
}
}
}
- 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;
- 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.
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)
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
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)
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