Date field filter much slower than manually written query

1

I am trying to build a dashboard with a manually entered date range. When I hard code in the query and write in something like this:

WHERE ("public"."session"."created_at" >= CAST((now() + (INTERVAL '-2 day')) AS date) AND "public"."session"."created_at" < CAST(now() AS date))

^ this executes in <1s. But when I replace the above section with

WHERE TRUE [[AND {{date_value}}]]

image
image706×954 34 KB

image

All of a sudden the exact same query (from my point of view) takes almost 30s. I generated the original WHERE statement using the metabase query generator. What is being generated behind the scenes that is so inefficient?

2

there shouldn't be so much difference. Can you check the query that metabase generates and do an explain analyze of both syntaxes?

3

How do I see the query that metabase generates?

4

Hey! You can view the SQL for any question if you open the query builder and click the little "console" icon in the top right (it will say "View the SQL" when you hover over it")!

5

Yes this I know. The query I generated using the query builder is working fine and responds in <1s. It the query written in native SQL with a date field filter that is not performing well. I assume this date field filter is being compiled to some form of SQL. How do I find this compilation? Because that’s the query that’s running slowly.

6

You can get this by profiling the database. Another option would be to check the new version where we’ve added a feature for this specific thing

Last option: do an “explain analyze” of the query

7

In my case the reason is that the query is executed using DateFromParts function on Date field of Database and then comparing it to filter value.

WHERE
DateFromParts(year("dbo"."TestTable"."Date"), month("dbo"."Test_Table"."Date"), day("dbo"."Test_Table"."Date")) = @P0

8

We've finally tracked the issue. It's because the field filter is using CAST("public"."player_games"."created_at" AS date) it's preventing our database from using the index on the created_at column. Which makes the query significantly slower than when just hard coding in date ranges. Which is annoying.

Is there any chance newer version of Metabase have better time based aggregation that doesn't block Postgres date indexes from being used? I'm using version 0.44.1

9

Interesting, first of all move to 47.8 and test again, but I think you're hitting Metabase field filter casts timestamp column instead of casting queried date · Issue #21133 · metabase/metabase · GitHub

10

Yes this sounds exactly like what I’m running into. It makes the date field filter (one our most desired filters) basically useless because it prevents our database from using the index. It turns queries that take seconds to run and converts it into a query that takes hours to run.