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