fetchTableListQuery too slow for legacy database

[stephane303]

graphql-engine/console/src/components/Services/Data/utils.js

Line 385 in 2f319d3

export const fetchTableListQuery = options => {

I have a problem with the fetchTableListQuery SQL used by Hasura, the request is extremely slow, more than one minute, and it prevents me to use the console as it seems the request is sent multiple times.

After some trial, I found out that if I remove the following line : ' where (ist.table_schema='public') ' near the end of the file, the problem is solved.

After more investigations I have seen that the pg_namespace table has a lot of pgtemp#, pg_toasttemp# entries, which slows down the fetch_schema query.
My company is using PostgreSql 9.6

I am willing to try to optimize the query, but I am just not sure how to build my own graphql-engine from github, is there any guide to help me ?

[0x777]

@rikinsk, Alexis (cc @lexi-lambda) has recently done a bunch of optimizations to the query that server uses to fetch metadata and found that Postgres versions < 12 generate very bad plans for queries which involve columns of type 'name'. Alexis, can you quickly glance at this whenever you have time to see if it the same issue?

@stephane303 https://github.com/hasura/graphql-engine/blob/master/CONTRIBUTING.md

[lexi-lambda]

This query seems to go through the information_schema views, which is likely slow even when name is used. One change that was made in Postgres 12 is that the information_schema.sql_identifier type was changed to use name under the hood, which makes using those views more efficient, but obviously that isn’t relevant for Postgres 9.6.

My guess is that upgrading to Postgres 12 could provide a performance boost, but that query should probably be optimized not to use information_schema, and it should probably be moved out of the console code into an API exposed by the server.

[dave-philp]

Hi @beerose - just checking in to see if there are any plans to include the optimisation from #4925 in a future release? We have a workaround in place at the moment for this exact issue.

[beerose]

Hi @intellischool-dave, this optimization works great for Yugabyte DBs, but the query performs worse for Postgres DBs. Thus we can't incorporate this change. We're working on a different solution for this.

[nvcnvn]

Just want to know if Hasura 2.0 improve this?
We're testing using alpha 7 on Yugabyte, the queries still sometime painfully slow.

[munjalpatel]

We are also experimenting with Hasura 2 + Yugabyte. I am impressed by Hasura. But, Hasura Console is simply unusable. Almost every query related to schema management ends up taking 30+ seconds.

cc: @coco98

[jannehietamaki]

This issue is originally about performance with Postgresql 9.6, but at the moment YugabyteDB is on Postgres 12 and the issue still persists with Hasura 2.

Here is a ticket on YugabyteDB side yugabyte/yugabyte-db#7745

Hasura and YugabyteDB would be a very sweet combination and Hasura Cloud UI even has some extra tools for YugabyteDB, but at the moment this issue makes using them together pretty painful. The products itself would work fine, but as initial developer experience is bad, it makes them both look bad and probably most people evaluating tools end up using something else.

I'm sure Yugabyte guys would be happy to help with this and it would be on both companies interest to get this fixed.

[jannehietamaki]

I was googling around, and looks like adding /*+ Set(enable_nestloop false) **/ hint to these two queries would make Hasura UI usable on YugabyteDB.

For example: SELECT /*+ Set(enable_nestloop false) **/ column_name, table_name, is_generated, is_identity, identity_generation FROM information_schema.columns where table_schema = 'public'; returns data in 100ms instead of 10s it takes without hint.

That other query also returns data after a couple of seconds, instead of timing out after 60 like it does without.