hasura / hasura/graphql-engine
Performance improvements for postgres query with a large array parameter
- Dominant language
- TypeScript
- Stars
- 32.1k
- Forks
- 3k
- PR merge metrics
- PR metrics pending
Description
We have a performance issue with a query that runs against large table
query GetSecurities($isin: [String!]) {
debtsecurities(
where: {
ISIN: {_in: $isin}
}
)
{
CUSIP
ISIN
Ticker
}
}
All database indexes are in place and data is in shared memory.
The database we use is postgres 14.x and we use hasura/graphql-engine:v2.41.0.cli-migrations-v2 docker image
The performance issue appears when $isin parameter has 10k+ or more items
SELECT
coalesce(json_agg("root"), '[]') AS "root"
FROM
(
SELECT
row_to_json(
(
SELECT
"_e"
FROM
(
SELECT
"_root.base"."CUSIP" AS "CUSIP",
"_root.base"."ISIN" AS "ISIN",
"_root.base"."Ticker" AS "Ticker"
) AS "_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"public"."Securities"
WHERE
(
("public"."Securities"."ISIN") = ANY(
(
'{"isin1",...,"isin10K+"}'
) :: varchar []
)
)
) AS "_root.base"
) AS "_root"
so the query runs for 2+ minutes
if the code inside ANY is replaced from array to a list of values like in the query below the results comes in subsecond
SELECT
coalesce(json_agg("root"), '[]') AS "root"
FROM
(
SELECT
row_to_json(
(
SELECT
"_e"
FROM
(
SELECT
"_root.base"."CUSIP" AS "CUSIP",
"_root.base"."ISIN" AS "ISIN",
"_root.base"."Ticker" AS "Ticker"
) AS "_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"public"."Securities"
WHERE
(
("public"."Securities"."ISIN") = ANY(
(
VALUES('isin1'), ('...'), ('isin10K')
)
)
) AS "_root.base"
) AS "_root"
The solution is described in the article here https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/
It would be cool to incorporate this into harusa connector
Contributor guide
Research direction
Start with the reported GraphQL query and the generated PostgreSQL SQL, then compare the array parameter used with ANY against the VALUES-based form described in the linked Datadog article. Reproduce the slowdown with 10k+ ISIN values on PostgreSQL 14 and the hasura/graphql-engine:v2.41.0.cli-migrations-v2 image. Done means the large-array query avoids the reported 2+ minute runtime without changing its results.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- graphql, postgresql
- Domain
- api, backend-api-design, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100