Performance tuning technique: joining subqueries

Performance tuning technique: joining subqueries

Performance tuning technique: joining subqueries

Hi. I finished an SQL course on udacity and the last lesson was about optimizing queries for better performance.

However, I didn’t fully understand the last exampled used in the course.

This is the database

‚Äč

https://preview.redd.it/skm2xgyf71j61.png?width=779&format=png&auto=webp&s=53d2917c4ac41496e48dda588210540b37388b18

They showed this query

SELECT DATE_TRUNC('day', o.occurred_at) AS date, COUNT(DISTINCT a.sales_rep_id) AS active_sales_reps, COUNT(DISTINCT o.id) AS orders, COUNT(DISTINCT w.id) AS visits FROM accounts a JOIN orders o ON a.id = o.account_id JOIN web_events w ON DATE_TRUNC('day', w.occurred_at) = DATE_TRUNC('day', o.occurred_at) GROUP BY 1 ORDER BY 1 DESC 

The guy said that this query generated a ‘data explosion’ because of the ON clause involving dates, and showed how if you removed the aggregations the query involved 79000 rows.

Then, they said that if you used subqueries and joined them, less rows would be involved so the performance would be better, and this was the final query

SELECT COALESCE(o.date, w.date) AS date, o.active_sales_reps, o.orders, w.visits FROM ( SELECT DATE_TRUNC('day', o.occurred_at) AS date, COUNT(a.sales_rep_id) AS active_sales_reps, COUNT(o.id) AS orders FROM accounts a JOIN orders o ON o.account_id = a.id GROUP BY 1 ) o FULL JOIN ( SELECT DATE_TRUNC('day', w.occurred_at) AS date, COUNT(w.id) AS visits FROM web_events w GROUP BY 1 ) w ON o.date = w.date ORDER BY 1 DESC 

I understand what they say and it makes sense, but then I run EXPLAIN in both queries and the first one has a very smaller cost than the second one, and I don’t understand why. AND apart from the EXPLAIN, if I run both queries they take almost the same time, the second one isn’t considerably faster than the first one.

Maybe I don’t understand the performance technique or maybe I don’t understand how EXPLAIN works.

Any help or advice appreciated.

submitted by /u/ipjac
[link] [comments]


Go to Source of this post
Author Of this post: /u/ipjac
Title Of post: Performance tuning technique: joining subqueries
Author Link: {authorlink}