Help diagnosing query times fast in the DB but slow when executed via the app

Questions / Help
1

Hi,

I have an SQL query that have recently massively increased in the time it takes to execute, the SQL for the query is:

SELECT
                                  b0."id",
                                  b0."additional_cancellation_reason",
                                  b0."additional_refund_reason",
                                  b0."booking_purpose_confirmed",
                                  b0."canceled", b0."canceled_at",
                                  b0."cancellation_reason",
                                  b0."duration",
                                  b0."ends_at_utc",
                                  b0."full_price",
                                  b0."incident_number",
                                  b0."include_equipment_in_price",
                                  b0."payment_id",
                                  b0."payment_id_from_handler",
                                  b0."power_on",
                                  b0."price_paid",
                                  b0."refund_reason",
                                  b0."refunded",
                                  b0."starts_at_local",
                                  b0."starts_at_utc",
                                  b0."status",
                                  b0."uuid",
                                  b0."studio_id",
                                  b0."user_id",
                                  b0."inserted_at",
                                  b0."updated_at"
                              FROM "booking"
                                  AS b0 INNER JOIN "studio"
                                  AS s1 ON s1."id" = b0."studio_id" INNER JOIN "site"
                                  AS s2 ON s2."id" = s1."site_id"
                                  WHERE ((b0."user_id" = 71099)
                                      AND (b0."canceled" = FALSE))
                                      AND (NOT (b0."status" IN ('awaiting_payment','initialised')))
                              ORDER BY b0."ends_at_utc" DESC LIMIT 500

When I execute this directly on our production DB, the execution time is around a quarter of a second.

When I type this query out verbatim and run MyRepo.all(query) in the production app server, this takes around 5 seconds. When I execute the same command in my local terminal it’s back down to a fraction of a second.

Is there something happening between our production servers and the database? They’re hosted on AWS but my infra knowledge is pretty limited, if anyone has any areas I could investigate that would be appreciated.

Thanks in advance,
Mark

2

Are you executing the query against production database or the local? as it is not entirely clear from your statement.

3

Sorry that wasn’t very clear, I’m getting times of under a quarter a second when I execute directly in postgres on both production and locally. When I execute in app locally with MyApp.Repo.all(query), this executes in under a second, and on production this is over 5.

Thanks again

4

Is this the query execution time for the Repo.all call (can be observed with telemetry) or the time the function executes? If the latter how long does Repo.query(sql, params) take? At best also Repo.checkout a connection in advance.

Are there nested associations involved? If yes, how are the ratios of parent record to children? Large ratios (and large number of parents) might mean ecto has a lot of deduplication to do to return a tree of structs from the tabular data the db returns. In such cases preloading using separate queries might be more performant.