Conditional join performance optimisation
M
Miles Rowland
This dbt transformation takes 4s to run on a Snowflake xs warehouse, but takes 5m+ on a standard duckling:
EXPLAIN ANALYZE
WITH journal AS (
SELECT
journal_entry_id,
date,
account_code,
amount_gbp,
source_type,
source_id,
branch_id
FROM sentinel_share.raw.journal_entries
),
invoices AS (
SELECT
invoice_id,
customer_id,
source_type,
source_id,
total_amount
FROM sentinel_share.raw.invoices
),
orders AS (
SELECT
order_id,
quote_id,
customer_id
FROM sentinel_share.raw.orders
),
quotes AS (
SELECT
quote_id,
customer_id
FROM sentinel_share.raw.quotes
),
projects AS (
SELECT
project_id,
customer_id
FROM sentinel_share.raw.projects
),
milestones AS (
SELECT
milestone_id,
project_id
FROM sentinel_share.raw.project_milestones
),
contracts AS (
SELECT
contract_id,
customer_id
FROM sentinel_share.raw.service_contracts
)
SELECT
je.journal_entry_id,
je.account_code,
je.amount_gbp,
je.source_type,
je.source_id,
je.branch_id,
i.invoice_id,
i.customer_id,
i.source_type AS invoice_source_type,
i.source_id AS invoice_source_id,
i.total_amount AS invoice_total_amount,
pm.project_id,
p.customer_id AS project_customer_id,
o.order_id,
o.quote_id AS order_quote_id,
q.quote_id AS spares_quote_id,
COALESCE(
i.customer_id,
p.customer_id,
q.customer_id,
o.customer_id,
sc.customer_id
) AS resolved_customer_id
FROM journal AS je
LEFT JOIN invoices AS i
ON je.source_id = i.invoice_id
AND je.account_code IN ('4000', '4010', '4020', '5000')
LEFT JOIN milestones AS pm
ON i.source_type = 'project'
AND i.source_id = pm.milestone_id
LEFT JOIN projects AS p
ON pm.project_id = p.project_id
LEFT JOIN quotes AS q
ON i.source_type IN ('spares', 'remedial')
AND i.source_id = q.quote_id
LEFT JOIN orders AS o
ON i.source_type IN ('spares', 'remedial')
AND i.source_id = o.quote_id
LEFT JOIN contracts AS sc
ON je.source_type = 'ppm'
AND je.source_id = sc.contract_id;
When i re-write the query to avoid the conditional join I believe the query planner is able to do a hash join rather than a bitwise join, and as a result it runs in 2s on a standard duckling:
EXPLAIN ANALYZE
WITH journal AS (
SELECT
journal_entry_id,
date,
account_code,
amount_gbp,
source_type,
source_id,
branch_id
FROM "sentinel_share"."raw"."journal_entries"
),
invoices AS (
SELECT
invoice_id,
customer_id,
source_type,
source_id,
total_amount
FROM "sentinel_share"."raw"."invoices"
),
project_invoice_context AS (
SELECT
i.invoice_id,
pm.project_id,
p.customer_id AS project_customer_id
FROM invoices i
LEFT JOIN "sentinel_share"."raw"."project_milestones" pm
ON i.source_id = pm.milestone_id
LEFT JOIN "sentinel_share"."raw"."projects" p
ON pm.project_id = p.project_id
WHERE i.source_type = 'project'
),
commercial_invoice_context AS (
SELECT
i.invoice_id,
o.order_id,
o.quote_id AS order_quote_id,
q.quote_id AS spares_quote_id,
coalesce(q.customer_id, o.customer_id) AS commercial_customer_id
FROM invoices i
LEFT JOIN "sentinel_share"."raw"."quotes" q
ON i.source_id = q.quote_id
LEFT JOIN "sentinel_share"."raw"."orders" o
ON i.source_id = o.quote_id
WHERE i.source_type IN ('spares', 'remedial')
),
invoice_context AS (
SELECT
i.invoice_id,
i.customer_id,
i.source_type AS invoice_source_type,
i.source_id AS invoice_source_id,
i.total_amount AS invoice_total_amount,
pic.project_id,
pic.project_customer_id,
cic.order_id,
cic.order_quote_id,
cic.spares_quote_id,
cic.commercial_customer_id
FROM invoices i
LEFT JOIN project_invoice_context pic
ON i.invoice_id = pic.invoice_id
LEFT JOIN commercial_invoice_context cic
ON i.invoice_id = cic.invoice_id
),
invoice_journal AS (
SELECT
je.journal_entry_id,
je.account_code,
je.amount_gbp,
je.source_type,
je.source_id,
je.branch_id,
ic.invoice_id,
ic.customer_id,
ic.invoice_source_type,
ic.invoice_source_id,
ic.invoice_total_amount,
ic.project_id,
ic.project_customer_id,
ic.order_id,
ic.order_quote_id,
ic.spares_quote_id,
ic.commercial_customer_id
FROM journal je
LEFT JOIN invoice_context ic
ON je.source_id = ic.invoice_id
WHERE je.account_code IN ('4000', '4010', '4020', '5000')
),
non_invoice_journal AS (
SELECT
journal_entry_id,
date,
account_code,
amount_gbp,
source_type,
source_id,
branch_id,
cast(null AS varchar) AS invoice_id,
cast(null AS varchar) AS customer_id,
cast(null AS varchar) AS invoice_source_type,
cast(null AS varchar) AS invoice_source_id,
cast(null AS double) AS invoice_total_amount,
cast(null AS varchar) AS project_id,
cast(null AS varchar) AS project_customer_id,
cast(null AS varchar) AS order_id,
cast(null AS varchar) AS order_quote_id,
cast(null AS varchar) AS spares_quote_id,
cast(null AS varchar) AS commercial_customer_id
FROM journal
WHERE account_code NOT IN ('4000', '4010', '4020', '5000')
OR account_code IS NULL
),
journal_with_invoice_context AS (
SELECT * FROM invoice_journal
UNION ALL
SELECT * FROM non_invoice_journal
),
ppm_contract_context AS (
SELECT
je.journal_entry_id,
sc.customer_id AS contract_customer_id
FROM journal je
LEFT JOIN "sentinel_share"."raw"."service_contracts" sc
ON je.source_id = sc.contract_id
WHERE je.source_type = 'ppm'
)
SELECT
je.* EXCLUDE (commercial_customer_id),
coalesce(
je.customer_id,
je.project_customer_id,
je.commercial_customer_id,
pc.contract_customer_id
) AS resolved_customer_id
FROM journal_with_invoice_context je
LEFT JOIN ppm_contract_context pc
ON je.journal_entry_id = pc.journal_entry_id;
Is it possible to have Motherduck conditional joins perform similarly to Snowflake?