Tweaking GraphQL performance using Postgres’ Explain command
Tags: graphql, postgres, appdevelopment, webdev
Published: 2019-12-24 11:30:26

---
title: Tweaking GraphQL performance using Postgres’ Explain command
published: true
date: 2018-12-03 00:00:00 UTC
tags: graphql, postgres, app development, webdev
canonical_url: https://blog.hasura.io/tweaking-graphql-performance-using-postgres-explain-command-6a3d84fd9c9a/
---

![](https://blog.hasura.io/content/images/downloaded_images/tweaking-graphql-performance-using-postgres-explain-command-6a3d84fd9c9a/1-kNkakwKusPEd8nvDs-kC9A.png)

### Leveraging Postgres’ execution plan information to find performance bottlenecks

As the Hasura GraphQL Engine’s API is meant to be consumed concurrently by many frontend client apps, we’ve invested a significant amount of effort into ensuring that Hasura is super performant (_in terms of latency and resource consumption_) and inherently scalable. In this post, let’s see how we introduced a performance analysis feature for users by leveraging Postgres’ popular execution plan analysis tool, `Explain`.

## Performance in Hasura GraphQL Engine

![](https://blog.hasura.io/content/images/downloaded_images/tweaking-graphql-performance-using-postgres-explain-command-6a3d84fd9c9a/1--U7I8Ek0GURsp5-KBAacNQ.png)<figcaption>The lifecycle of a query in the GraphQL Engine world</figcaption>

An incoming request to Hasura goes through the following stages:

1. The request body is parsed into a GraphQL AST
2. The GraphQL query is validated (_checks for semantic correctness and ACL/authorization_), and, if it passes all checks,
3. The validated query is converted to an SQL statement and is executed by Postgres (_which also converts the resultset into JSON_)
4. The JSON response is returned to the client.

We have previously blogged about the [architectural decisions made for optimizing the performance](https://blog.hasura.io/architecture-of-a-high-performance-graphql-to-sql-server-58d9944b8a87) of the Hasura GraphQL Engine. Network latencies are also negligible as Hasura and the configured Postgres instance are meant to be collocated in the same datacenter/network. So, that leaves database optimizations, like indexes, etc. as options critical to improving the performance of the GraphQL API. These optimizations are largely dependent on the database schema and the quantum of data being handled.

Postgres’ `Explain` command displays the execution plan generated for the supplied SQL statement. The execution plan shows how the table(s) referenced by the statement will be scanned, and if multiple tables are referenced, what join algorithms will be used to bring together the required rows from each input table, along with estimated statement execution cost ([_Postgres docs_](https://www.postgresql.org/docs/10/sql-explain.html)_; check out this_ [_post_](https://robots.thoughtbot.com/reading-an-explain-analyze-query-plan) _on how to interpret a Postgres execution plan_).

{% gist https://gist.github.com/dsandip/dbb43bd86434ac13b3ed42d3cb3284de.js %}

In a [recent version](https://github.com/hasura/graphql-engine/releases/tag/v1.0.0-alpha25) of Hasura GraphQL Engine, we introduced an API that takes a GraphQL query as input and outputs the generated SQL along with the execution plan prepared by the `Explain` command.

![](https://blog.hasura.io/content/images/downloaded_images/tweaking-graphql-performance-using-postgres-explain-command-6a3d84fd9c9a/1-9eRppoBYxTMZRMnLkF_jtw.png)<figcaption>The generated SQL and the execution plan for a given GraphQL query</figcaption>

Let’s see how this feature helps tweak the performance of GraphQL queries through an anecdote.

## From 30s → less than 1s

A user from the community reported alarmingly high latency numbers for one of their queries — 30 seconds! The query fetches data from two tables, `MrSeries` and `MrVolume` **,** each with a few million rows in it. Here’s a modified version of that query:

{% gist https://gist.github.com/dsandip/b2b48887b16a20bb34d63dbc3f564c81.js %}

And, here’s the corresponding execution plan for it from the `Explain` command:

{% gist https://gist.github.com/dsandip/a5581c9a70d57c78862a3208ee8c8a6c.js %}

Performance bottlenecks are the steps with high associated costs (_cost is an arbitrary unit of computation_). In the above execution plan, it’s easy to see that the bottlenecks are lines 11 and 14 — Postgres running `sequential scans` on the tables `MrSeries` and `MrVolume` **,** which is bad as there are millions of rows in them! To rectify this, we added a couple of indexes on these tables:

<script src="https://gist.github.com/dsandip/78c9070f77e9bffa2d98df23bc36efa1.js"></script>

Then, we ran the following SQL commands to update Postgres’ stats about these tables:

{% gist https://gist.github.com/dsandip/208ca3d1c715eb66b504d09566c4cd79.js %}

When we ran and analyzed the query again, here’s what we saw:

{% gist https://gist.github.com/dsandip/120cec3c0c01af4cf51349c3d3c7dc22.js %}

Notice how the aggregate cost came down from 2439653.09 to 174088.71 (_a ~14x drop!_). The **latency also went down from ~30 seconds to less than 1 second**! All this from just adding a couple of indexes. How awesome is that?! 🎈🎉🎈

We are iterating on this feature to make the insights from `Explain` readily actionable, automatically highlight performance bottlenecks, and perhaps even automate the database admin tasks for developers! 😉

### How to check out this feature

[Deploy](https://docs.hasura.io/1.0/graphql/manual/getting-started/index.html) the Hasura GraphQL Engine ([Heroku](https://docs.hasura.io/1.0/graphql/manual/getting-started/heroku-simple.html) is the fastest way to try it out) and create a table or two using the admin console (_also seed it with some test data_). Head to the GraphiQL tab of the console and run any valid GraphQL query based on your tables. Click on the `Analyze` button to see the execution plan and the generated SQL for your GraphQL query!

![](https://blog.hasura.io/content/images/downloaded_images/tweaking-graphql-performance-using-postgres-explain-command-6a3d84fd9c9a/1-tFSwKhL9T6JFQpIllcbGuw.png)<figcaption> command built into the GraphiQL interface</figcaption>

Do take this feature for a spin and let us know what you think. We’d love to hear your thoughts on performance for GraphQL APIs in our very active [Discord community channel](https://discord.gg/vBPpJkS)!