Execution time of Metabase UI queries compared to native queries

Get Help
1

The query will take about a few seconds to execute if you create a query in Metabase Question and then convert it to SQL using the button "View in SQL"; on the other hand, the result will take much longer than a few minutes to execute a raw query written using the "Native query" option.

This is the raw query of mine,

SELECT *  FROM "public"."Transactions" p
WHERE p."Date" BETWEEN  '2023-08-1' AND '2023-08-14'
ORDER BY p."Date" DESC

and the second query is that of Metabase's:

 SELECT   "public"."Transactions"."Id"               AS "Id",
         "public"."Transactions"."UserId"           AS "UserId",
         "public"."Transactions"."Amount"           AS "Amount",
         "public"."Transactions"."X"     AS "X",
         "public"."Transactions"."Date"          AS "Date",
         "public"."Transactions"."isdg"           AS "isdg",
         "public"."Transactions"."tgtg"        AS "tgtg",
         "public"."Transactions"."rtttt"         AS "rtttt",
         "public"."Transactions"."Code"             AS "Code",
         "public"."Transactions"."asas"        AS "asas",
         "public"."Transactions"."C"            AS "C",
         "public"."Transactions"."D"          AS "D",
         "public"."Transactions"."E"            AS "E",
         "public"."Transactions"."FF"     AS "FF",
         "public"."Transactions"."BB"        AS "BB",
         "public"."Transactions"."tyty"         AS "tyty",
         "public"."Transactions"."rtrte"      AS "rtrte",
         "public"."Transactions"."vvvi"       AS "vvvi",
         "public"."Transactions"."gggi"   AS "gggi",
         "public"."Transactions"."opo"         AS "opo",
         "public"."Transactions"."rrrr"        AS "rrrr",
         "public"."Transactions"."rere"           AS "rere",
         "public"."Transactions"."HH"    AS "HH",
         "public"."Transactions"."kl"            AS "kl",
         "public"."Transactions"."ppp"    AS "ppp",
         "public"."Transactions"."oo"     AS "oo",
         "public"."Transactions"."Y"     AS "Y",
         "public"."Transactions"."ee"        AS "ee",
         "public"."Transactions"."qq"    AS "qq",
         "public"."Transactions"."JJ"    AS "JJ",
         "public"."Transactions"."TTT" AS "TTT",
         "public"."Transactions"."ll"     AS "ll",
         "public"."Transactions"."UUU"     AS "UUU"
FROM     "public"."Transactions"
WHERE    (
                  "public"."Transactions"."Date" >= timestamp with time zone '2023-08-01 00:00:00.000Z')
AND      (
                  "public"."Transactions"."Date" < timestamp WITH time zone '2023-08-13 00:00:00.000Z')
ORDER BY "public"."Transactions"."Date" ASC

Could there be any explanation for this situation?

2

I'd guess the problem is your date expressing in the WHERE clause.
Change that to match that in Metabase's query to test. Could be casting all the datetimes in the DB to either dates or varchar. Should be obvious if you copy the query into something else and look at the plan.

3

version?

4

Metabase version v0.46.7

5

Please run explain analyze on both queries

6

My fault, I filtered my query on an unindexed field date!

Index Scan Backward using "ix_payDate" on "Transactions" p (cost=0.56..17566.88 rows=69176 width=1204) (actual time=0.542..1154.388 rows=203321 loops=1)

Index Cond: (("PayDate" >= '2023-08-01 00:00:00'::timestamp without time zone) AND ("PayDate" <= '2023-08-14 00:00:00'::timestamp without time zone))

Planning time: 1.664 ms

7

I don’t see the execution time, also it’s just one query

8

Merci Luiggi, I've solved it. We can close this topic. I accidentally filtered an unindexed field.