@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!