Performance problems with nested queries, 0.45.2

Get Help
#1

Hi there,

We've just moved from 0.41.2 to 0.45.2, and some of our questions which used to return in a few seconds are now taking so long we mostly get 'Your question took too long' timeouts.

The affected queries all have the same structure:

  • an underlying question written in SQL and returning a largeish dataset (<20,000 rows)
  • multiple questions all based on the same underlying one, constructed in the GUI (using 'Explore results') and filtering/summarizing the first dataset

Viewing the (first 2000) results of the underlying question directly takes 4-5 seconds (also slower than 0.41.2, and direct db query takes 1.1s, but that's a separate topic).

My workaround is to convert all the secondary questions into native queries, but it's not ideal having to duplicate the complex underlying query!

I found Performance issue when using nested questions, which returns ~100k+ results · Issue #13572 · metabase/metabase · GitHub which sounds similar but was fixed before 0.41.2 - could there be a regression here?

Metabase 0.45.2 (Docker, in AWS ECS)
Postgres 13.7 (AWS RDS)

Thanks for your time!
Phil

#2

So if you leave the questions as GUI everything's slow, but if you move them to native SQL they work correctly?

#3

Sorry for my delay getting back to this. Apologies for the false report, this turns out to be a Postgres issue (our manual conversion to native queries wasn't doing the same as Metabase's conversion, otherwise our queries would also have been slow). We'd also changed Postgres version at the same time as upgrading Metabase :woman_facepalming:

In case it's useful for anybody else:

complex query

~20,000 rows in ~4s

select * from (
   complex query
) x
where x.field is null

~15,000 rows in ~6s, Postgres 9 (AWS RDS Serverless 1)
hasn't finished in 10 minutes, Postgres 13 (AWS RDS Serverless 2)

I'll follow that up somewhere more relevant - thanks!