What you should draw your attention to are the COST values. While these are arbitrary, they are not meaningless. Find the section of the query where this grows the largest, that is the slowest part of the query in need of optimization. (Save yourself some time as search for other_publisher_views) As this query had been previously reviewed for proper indexing, all queries should be indexed so we need to find another optimization. Since this is already indexed, draw your attention to the ROWS
What you should draw your attention to are the COST values. While these are arbitrary, they are not meaningless. Find the section of the query where this grows the largest, that is the slowest part of the query in need of optimization. (Save yourself some time as search for other_publisher_views) As this query had been previously reviewed for proper indexing, all queries should be indexed so we need to find another optimization.
Since this is already indexed, draw your attention to the ROWS returned. If you look at the next query which makes use of these results, you'll notice the ROWS is much smaller. So our problem is we are looking at way more data than we need to. We are pulling over 100,000 rows when we are trying to find 4 of them.
Now let's extract the critical section to work with, along with simplified versions of its dependencies:
WITH
listing_info_raw AS (
SELECT
'' AS property_id_sha,
'' AS mc_export_property_id_sha,
'(Rental)' AS pretty_address,
' (Rental)' AS display_address
),
periods AS (
SELECT
true AS is_current_period,
CAST('2020-11-09' AS date) AS start_date,
CAST('2020-11-15' AS date) AS end_date
),
other_publisher_views AS (
SELECT clv.date,
clv.streeteasy_page_views,
clv.trulia_page_views,
clv.zillow_page_views,
clv.realtor_page_views
FROM agent_insights_lt.agent_insights__combined_listing_views AS clv
JOIN listing_info_raw ON clv.property_id_sha = listing_info_raw.property_id_sha
),
other_publisher_views_by_period AS (
SELECT is_current_period,
start_date,
end_date,
coalesce(sum(streeteasy_page_views), 0) AS streeteasy_page_views,
coalesce(sum(trulia_page_views), 0) AS trulia_page_views,
coalesce(sum(zillow_page_views), 0) AS zillow_page_views,
coalesce(sum(realtor_page_views), 0) AS realtor_page_views
FROM periods
LEFT JOIN other_publisher_views ON
other_publisher_views.date BETWEEN periods.start_date AND periods.end_date
)
SELECT * FROM other_publisher_views_by_period;
The task for this lab is to optimize this section of the query, which includes other_publisher_views & other_publisher_views_by_period.
Submit your optimized version of this section of the query.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
