Replies: 2 comments
-
|
In the GraphiQL tab you have the Analyze button This will give you the Hasura generated SQL and the execution plan. Without it is hard to debug what the issue could be. Can you post the results from your execution plan and the generated SQL? |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Thanks! Here is the generated sql and execution plan:
My SQL:
SELECT c.text_value, i.source_url, r.pos AS pos
FROM bpetersob.cells c
JOIN bpetersob.records r ON (c.record_id = r.id)
LEFT JOIN bpetersob.cells c2 ON (c.id = c2.parent_id)
LEFT JOIN bpetersob.images i ON (c2.image_id = i.id)
WHERE c.record_id IN (SELECT id FROM bpetersob.records
ORDER BY pos
LIMIT 10
OFFSET 80000
)
ORDER BY pos
My Graphql:
query MyQuery {
bpetersob_records(order_by: {pos: asc}, limit: 10, offset: 80000) {
cells {
text_value
children {
cell_image {
source_url
}
}
}
}
}
Generated SQL:
SELECT
coalesce(
json_agg(
"root"
ORDER BY
"root.pg.pos" ASC NULLS LAST
),
'[]'
) AS "root"
FROM
(
SELECT
"_0_root.base"."pos" AS "root.pg.pos",
row_to_json(
(
SELECT
"_12_e"
FROM
(
SELECT
"_11_root.ar.root.cells"."cells" AS "cells"
) AS "_12_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"bpetersob"."records"
WHERE
('true')
) AS "_0_root.base"
LEFT OUTER JOIN LATERAL (
SELECT
coalesce(json_agg("cells"), '[]') AS "cells"
FROM
(
SELECT
row_to_json(
(
SELECT
"_9_e"
FROM
(
SELECT
"_1_root.ar.root.cells.base"."text_value" AS
"text_value",
"_8_root.ar.root.cells.ar.cells.children"."children" AS "children"
) AS "_9_e"
)
) AS "cells"
FROM
(
SELECT
*
FROM
"bpetersob"."cells"
WHERE
(("_0_root.base"."id") = ("record_id"))
) AS "_1_root.ar.root.cells.base"
LEFT OUTER JOIN LATERAL (
SELECT
coalesce(json_agg("children"), '[]') AS "children"
FROM
(
SELECT
row_to_json(
(
SELECT
"_6_e"
FROM
(
SELECT
"_5_root.ar.root.cells.ar.cells.children.or.cell_image"."cell_image" AS
"cell_image"
) AS "_6_e"
)
) AS "children"
FROM
(
SELECT
*
FROM
"bpetersob"."cells"
WHERE
(
("_1_root.ar.root.cells.base"."id") =
("parent_id")
)
) AS "_2_root.ar.root.cells.ar.cells.children.base"
LEFT OUTER JOIN LATERAL (
SELECT
row_to_json(
(
SELECT
"_4_e"
FROM
(
SELECT
"_3_root.ar.root.cells.ar.cells.children.or.cell_image.base"."source_url"
AS "source_url"
) AS "_4_e"
)
) AS "cell_image"
FROM
(
SELECT
*
FROM
"bpetersob"."images"
WHERE
(
(
"_2_root.ar.root.cells.ar.cells.children.base"."image_id"
) = ("id")
)
) AS
"_3_root.ar.root.cells.ar.cells.children.or.cell_image.base"
) AS
"_5_root.ar.root.cells.ar.cells.children.or.cell_image" ON ('true')
) AS "_7_root.ar.root.cells.ar.cells.children"
) AS "_8_root.ar.root.cells.ar.cells.children" ON ('true')
) AS "_10_root.ar.root.cells"
) AS "_11_root.ar.root.cells" ON ('true')
ORDER BY
"root.pg.pos" ASC NULLS LAST
LIMIT
10 OFFSET ('80000') :: integer
) AS "_13_root"
Execution Plan:
Aggregate (cost=10909818.65..10909818.66 rows=1 width=32)
-> Limit (cost=10908454.99..10909818.52 rows=10 width=40)
-> Nested Loop Left Join (cost=136.56..13723619.26 rows=100646
width=40)
-> Index Scan using records_pos_idx on records
(cost=0.29..4142.06 rows=100646 width=24)
-> Aggregate (cost=136.27..136.28 rows=1 width=32)
-> Nested Loop Left Join (cost=29.32..136.21 rows=4
width=56)
-> Index Scan using cells_record_id_idx on cells
(cost=0.42..20.49 rows=4 width=40)
Index Cond: (record_id = records.id)
-> Aggregate (cost=28.90..28.91 rows=1 width=32)
-> Nested Loop Left Join
(cost=0.57..28.87 rows=2 width=32)
-> Index Scan using
cells_parent_id_idx on cells cells_1 (cost=0.42..12.46 rows=2 width=16)
Index Cond: (parent_id =
cells.id)
-> Index Scan using images_pkey on
images (cost=0.15..8.18 rows=1 width=48)
Index Cond: (id =
cells_1.image_id)
SubPlan 3
-> Result (cost=0.00..0.01
rows=1 width=32)
SubPlan 4
-> Result (cost=0.00..0.01 rows=1
width=32)
SubPlan 2
-> Result (cost=0.00..0.01 rows=1 width=32)
SubPlan 1
-> Result (cost=0.00..0.01 rows=1 width=32)
JIT:
Functions: 34
Options: Inlining true, Optimization true, Expressions true, Deforming
true
…On Tue, Jul 13, 2021 at 5:07 PM Leonardo Alves ***@***.***> wrote:
In the GraphiQL tab you have the Analyze button
[image: image]
<https://user-images.githubusercontent.com/3867140/125540510-51fc9a31-6d9b-4c89-a5bc-2d69ff906ba9.png>
This will give you the Hasura generated SQL and the execution plan.
Without it is hard to debug what the issue could be. Can you post the
results from your execution plan and the generated SQL?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
<#7226 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AALDFFFZOMJH4P4FBPVYURLTXTIKZANCNFSM5AH2LLTA>
.
--
🤓 βπ
|
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
I have been doing some preliminary investigation on a self-referential schema. My initial results have not been great. The graphql response times are about an order of magnitude slower than the raw sql queries on the same db. The queries do not provide the exact same information, but they are equivalent for my purposes. Any suggestions?
Thanks.
Layout of the test schema:
Records Cells Images
======= ===== ========
|-E id url
id ----E record_id width
pos |-- parent_id height
image_id --- id
text_value
Cells can have one of several types of values (including an image id) or can be a list that has items that point to the list via the parent_id.
Tested response times for random offsets.
Graphql
query MyQuery {
records(order_by: {pos: asc}, limit: 10, offset: <random(0 to 80,000)>) {
cells {
text_value
children {
cell_image {
url,
width,
height
}
}
}
}
}
Stats for query type: graphql
Number of trials: 12
Mean response time: 3.5063124895095825 secs
Standard deviation: 2.0418562159687252 secs
Mean offset: 35148.833333333336
Standard deviation: 26757.356282757533
SQL
SELECT c.text_value, i.url, i.width, i.height, c.parent_id, r.pos AS pos
FROM Cells c
JOIN Records r ON (c.record_id = r.id)
LEFT JOIN Cells c2 ON (c.id = c2.parent_id)
LEFT JOIN Images i ON (c2.image_id = i.id)
WHERE c.record_id IN (SELECT id FROM Records
ORDER BY pos
LIMIT 10
OFFSET <random(0 to 80,000)>
)
ORDER BY pos
Stats for query type: raw_sql
Number of trials: 12
Mean response time: 0.3258865475654602 secs
Standard deviation: 0.046025823558206974 secs
Mean offset: 40532.416666666664
Standard deviation: 31219.133400290782
Beta Was this translation helpful? Give feedback.
All reactions