# Dremio query forming
**URL:** https://community.dremio.com/t/dremio-query-forming/10498
**Category:** Uncategorized
**Created:** [April 10, 2023, 4:11pm UTC](https://community.dremio.com/t/dremio-query-forming/10498 "2023-04-10T16:11:17Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [April 10, 2023, 4:11pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/1 "2023-04-10T16:11:17Z")
Hello! I am performing the following SQL-Query on a Postgres database.
select
l\_returnflag,
l\_linestatus,
sum(l\_quantity) as sum\_qty,
sum(l\_extendedprice) as sum\_base\_price,
sum(l\_extendedprice \* (1 - l\_discount)) as sum\_disc\_price,
sum(l\_extendedprice \* (1 - l\_discount) \* (1 + l\_tax)) as sum\_charge,
avg(l\_quantity) as avg\_qty,
avg(l\_extendedprice) as avg\_price,
avg(l\_discount) as avg\_disc,
count(\*) as count\_order
from
lineitem
where
l\_shipdate \<= date ‘1998-12-01’ - interval ‘90’ day
group by
l\_returnflag,
l\_linestatus
order by
l\_returnflag,
l\_linestatus;
Everything works as expected. But when I look into the postgres server query store, I see that Dremio is forming my initial SQL-Query into a slightly different one. This is the SQL Query that Dremio performs on my postgres server.
SELECT
“l\_returnflag”,
“l\_linestatus”,
CAST(
CASE WHEN COUNT(“l\_quantity”) = $ 1 THEN $ 2 ELSE CAST(SUM(“l\_quantity”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum\_qty”,
CAST(
CASE WHEN COUNT(“l\_extendedprice”) = $ 3 THEN $ 4 ELSE CAST(SUM(“l\_extendedprice”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum\_base\_price”,
CAST(
CASE WHEN COUNT(“$f4”) = $ 5 THEN $ 6 ELSE CAST(SUM(“$f4”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum\_disc\_price”,
CAST(
CASE WHEN COUNT(“$f5”) = $ 7 THEN $ 8 ELSE CAST(SUM(“$f5”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum\_charge”,
(
CAST(
CASE WHEN COUNT(“l\_quantity”) = $ 9 THEN $ 10 ELSE CAST(SUM(“l\_quantity”) AS NUMERIC(38, 6)) END AS DOUBLE PRECISION
) / COUNT(“l\_quantity”)
) AS “avg\_qty”,
(
CAST(
CASE WHEN COUNT(“l\_extendedprice”) = $ 11 THEN $ 12 ELSE CAST(SUM(“l\_extendedprice”) AS NUMERIC(38, 6)) END AS DOUBLE PRECISION
) / COUNT(“l\_extendedprice”)
) AS “avg\_price”,
(
CAST(
CASE WHEN COUNT(“l\_discount”) = $ 13 THEN $ 14 ELSE CAST(SUM(“l\_discount”) AS NUMERIC(38, 6)) END AS DOUBLE PRECISION
) / COUNT(“l\_discount”)
) AS “avg\_disc”,
COUNT(\*) AS “count\_order”
FROM
(
SELECT
“l\_returnflag”,
“l\_linestatus”,
“l\_quantity”,
“l\_extendedprice”,
(“l\_extendedprice” \* ((1 - “l\_discount”))) AS “$f4”,
(
(“l\_extendedprice” \* ((1 - “l\_discount”))) \* ((1 + “l\_tax”))
) AS “$f5”,
“l\_discount”
FROM
(
SELECT
“lineitem”.“l\_quantity”,
“lineitem”.“l\_extendedprice”,
“lineitem”.“l\_discount”,
“lineitem”.“l\_tax”,
“lineitem”.“l\_returnflag”,
“lineitem”.“l\_linestatus”,
“lineitem”.“l\_shipdate”
FROM
“public”.“lineitem”
) AS “lineitem”
WHERE
“l\_shipdate” \<= DATE ‘1998-12-01’ - interval ‘90’ day
) AS “lineitem”
GROUP BY
“l\_returnflag”,
“l\_linestatus”
ORDER BY
“l\_returnflag”,
“l\_linestatus”
Is there any documentation on why Dremio does that? And especially why forming the initial statement to this particular one?
Thanks for any help!
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [April 11, 2023, 3:53pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/2 "2023-04-11T15:53:57Z")
@vincent_mayer Dremio uses Advanced Relational Pushdown (ARP) framework to rewrite the SQL in native RDBMS language as sometimes the SQL written on Dremio may be using something that is not available in Postgres. If you do not want Dremio to pushdown using ARP then you can use the External query feature
[https://docs.dremio.com/cloud/sql/commands/copy-into-table/](https://docs.dremio.com/cloud/sql/commands/copy-into-table/)
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [April 12, 2023, 12:24pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/3 "2023-04-12T12:24:25Z")
Thanks! Do you know where I can learn more about the ARP Framework?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [April 12, 2023, 11:06pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/4 "2023-04-12T23:06:12Z")
@vincent_mayer Does the OSS code does not help?
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [April 14, 2023, 10:43am UTC](https://community.dremio.com/t/dremio-query-forming/10498/5 "2023-04-14T10:43:30Z")
@balaji.ramaswamy I think got it. How does the forming work for a NoSQL database, e.g. MongoDB? I can find the driver but there is no arp.yaml file where the rules for forming are specified.
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [April 14, 2023, 12:58pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/6 "2023-04-14T12:58:37Z")
@balaji.ramaswamy Do you also know how Dremio performs queries on NoSQL and SQL Databases simultaneously? How does Dremio connect data from a SQL and a NoSQL Database and generate the result?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [April 17, 2023, 6:00am UTC](https://community.dremio.com/t/dremio-query-forming/10498/7 "2023-04-17T06:00:18Z")
@vincent_mayer Dremio will generate the pushdown separately to the 2 sources and the JOIN will happen in memory on the Dremio executors
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [April 21, 2023, 8:13am UTC](https://community.dremio.com/t/dremio-query-forming/10498/8 "2023-04-21T08:13:37Z")
@balaji.ramaswamy Is there any documentation on how the join will happen in memory on the Dremio executors?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [April 24, 2023, 6:33pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/9 "2023-04-24T18:33:23Z")
@vincent_mayer Is your question related to which type of exchange happens during a join like hashtorandom or broadcast etc?
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [May 1, 2023, 2:18pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/10 "2023-05-01T14:18:00Z")
@balaji.ramaswamy Yes 🙂
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 1, 2023, 3:01pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/11 "2023-05-01T15:01:04Z")
@vincent_mayer Look at sys.options for the threshold for broadcast and that is one of the major input factors that affects the type of the exchange, let me know if you are unable to find the key
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [May 2, 2023, 1:53pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/12 "2023-05-02T13:53:06Z")
@balaji.ramaswamy where can I find sys.options?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 4, 2023, 6:05am UTC](https://community.dremio.com/t/dremio-query-forming/10498/13 "2023-05-04T06:05:06Z")
@vincent_mayer Table in Dremio
---
### Author:  [@hmarchman-jones](https://community.dremio.com/u/hmarchman-jones)
#### Post date: [May 5, 2023, 11:33pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/14 "2023-05-05T23:33:48Z")
@vincent_mayer have you tried using the table(external\_query()) function?
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [May 6, 2023, 8:29am UTC](https://community.dremio.com/t/dremio-query-forming/10498/15 "2023-05-06T08:29:14Z")
@balaji.ramaswamy Can you tell me how to find the key?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 7, 2023, 9:36pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/16 "2023-05-07T21:36:32Z")
@vincent_mayer Would you be able to send me the profile so I can look at the plan and see what is going on?
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [May 8, 2023, 9:01am UTC](https://community.dremio.com/t/dremio-query-forming/10498/17 "2023-05-08T09:01:43Z")
@balaji.ramaswamy
[9942af70-e17e-4a1b-a9f9-b59e1d2e15a9.zip](https://community.dremio.com/uploads/short-url/tDntD1ikKyB6EokycMi6ycCLau5.zip) (32,3 KB)
This is the job profile. My questions basically are:
- What part of the system decides which data gets joined?
- And what criteria does the system use to pick the right join?
Glad for any help regarding these questions.
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 8, 2023, 9:53pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/18 "2023-05-08T21:53:44Z")
@vincent_mayer
Is the below push down not using the right SQL plan on your backend DB?
```auto
SELECT "part"."p_partkey" AS "p_partkey", "part"."p_name", "part"."p_mfgr" AS "p_mfgr", "part"."p_brand" AS "p_brand", "part"."p_type", "part"."p_size" AS "p_size", "part"."p_container" AS "p_container", "part"."p_retailprice" AS "p_retailprice", "part"."p_comment", "supplier"."s_suppkey", "supplier"."s_name", "supplier"."s_address" COLLATE "C" AS "s_address", "supplier"."s_nationkey", "supplier"."s_phone", "supplier"."s_acctbal", "supplier"."s_comment" COLLATE "C" AS "s_comment"
FROM (SELECT "part"."p_partkey", "part"."p_name" COLLATE "C" AS "p_name", "part"."p_mfgr", "part"."p_brand", "part"."p_type" COLLATE "C" AS "p_type", "part"."p_size", "part"."p_container", "part"."p_retailprice", "part"."p_comment" COLLATE "C" AS "p_comment"
FROM "public"."part"
WHERE "part"."p_size" = 15 AND "part"."p_type" COLLATE "C" LIKE '%BRASS' COLLATE "C") AS "part"
INNER JOIN "public"."supplier" ON TRUE
```
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [May 9, 2023, 8:59am UTC](https://community.dremio.com/t/dremio-query-forming/10498/19 "2023-05-09T08:59:48Z")
@balaji.ramaswamy To be honest, I dont know. How can I check?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 10, 2023, 11:34pm UTC](https://community.dremio.com/t/dremio-query-forming/10498/20 "2023-05-10T23:34:25Z")
@vincent_mayer Let us try something else
Can you please try running the above SQL directly on your backend DB and see how much time it takes?
[Next page](https://community.dremio.com/t/dremio-query-forming/10498.md?page=2)