Excessive Execution Time for Custom Question Based on Native Query

I have a Raw Query called "Actions" which gets data from an external CRM. The data represented amounts to 1.6 million rows and Metabase accomplishes this (loading 2000 rows of this data) instantly.

The problem is that once I am trying to build another Custom Question on top of this Raw Query, such as "Average Donation", I am not getting a response within a reasonable time. I am assuming it is because this would require loading 1.6 million rows, rather than just 2000, before running additional manipulations on it.

I am wondering if it is a reasonable to try and fix this issue by upgrading my Postgres.
I am hosting on heroku, and currently using the Standard 0 Postgres plan as recommended by the Metabase Heroku guide, which has 64GB storage and 4GB RAM, but there are options with higher storage and RAM.

Any other recommendations would also be appreciated.

Running Metabase v0.39.3

Hi @yonatan
If you look at the query that your database source receives, that should give you some insights into why it's taking longer.
It probably isn't using any indexes anymore, which then mean your database will generate a lot of temporary tables in memory to try to do the calculations.
Metabase does not do calculations - it sends the query to your data source. (With the exception of cumulative calculations)

Thanks, @flamber
I ran "show full processlist" on my database server, and found the query that was received.
In this example, I ran a Custom Question for the Count of rows:

SELECT count(*) AS `count` FROM (select 
    -- query omitted
    
from
(
    -- nested query omitted
) actions) `source` LIMIT 10

Is LIMIT 10 inserted by Metabase as it divides the query up into smaller chunks? If so, this would result in 160 thousand queries (since the table has 1.6 million rows).

Is there any way to work around this? Thank you.

@yonatan

Post "Diagnostic Info" from Admin > Troubleshooting, and which database type you're querying.

I don't know where the LIMIT 10 comes from, but I'm guessing somewhere you added that?

@flamber, thanks again.
I will post the diagnostic info shortly. However, I think I have found the source of the problem.
Custom questions rely on nested queries, and this query itself takes too long. When I run the native query on its own, Metabase implicitly adds limit 2000 and that is why the execution time is reasonable.
I would like to allow my non-SQL team members to build Custom Questions based on the large table, by filtering the table. However, when I filter through the Custom Questions interface the query takes excessively longer, than when I do so through the native query builder. This seems to be because the WHERE clause sits outside of the nested query like so:

SELECT count(*) AS `count`
FROM (
    -- query omitted, returns 1.6 million rows
from
(
    -- nested query omitted
) actions) `source`
WHERE `source`.`page_name` = 'example_page'

However, when I run the same query with the WHERE clause within the query, as shown below, it runs within 1 second or less:

SELECT count(*) AS `count`
FROM (
    -- query omitted, returns 4000 rows
from
(
    -- subquery omitted
) actions
WHERE actions.page_name = 'example_page') `source`

It seems clear that this has to do with the query returning only 4000 rather than 1.6 million. However: I am wondering if there exists a workaround for this.
I noticed that there wasn't a way to propagate variables from Native Queries to Custom Questions. (@flamber, thanks for your contributions on the topic).
But I was wondering if there was another workaround, since I do not know the details of SQL execution very well.
Thanks!

Diagnostic info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.106 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "1.8.0_292-heroku-b10",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_292-heroku",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "25.292-b10",
    "os.name": "Linux",
    "os.version": "4.4.0-1093-aws",
    "user.language": "en",
    "user.timezone": "Etc/UTC"
  },
  "metabase-info": {
    "databases": [
      "googleanalytics",
      "mysql",
      "mongo",
      "h2"
    ],
    "hosting-env": "heroku",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.3 (Ubuntu 13.3-1.pgdg20.04+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.18"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.39.3",
      "date": "2021-05-27",
      "branch": "release-x.39.x",
      "hash": "598a112"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

@yonatan But that would require Metabase to be able to parse SQL if it where to modify the SQL.
That is not currently possible. It is something we're working on, but it is difficult - very difficult - since Metabase supports much more than just MySQL as your query example shows.

The workaround is to create Views on the database, instead of SQL questions.

Thanks, @flamber. That's clarifying.

From my understanding, a View is a virtual table, and it needs to run its underlying query every time its accessed. It would seem that this would not make the execution time any shorter. Is that correct?

@yonatan If you created a View instead of a nested question, then filtering would be done similar to a regular table.

@flamber, thanks! I'm looking into that possibility.