explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tl5P

Settings
# exclusive inclusive rows x rows loops node
1. 266.736 11,110.347 ↓ 24,802.6 124,013 1

WindowAgg (cost=208,356.10..208,356.16 rows=5 width=219) (actual time=10,837.119..11,110.347 rows=124,013 loops=1)

2.          

CTE base_date_by_sku

3. 21.284 973.888 ↓ 389.5 3,895 1

Unique (cost=3,767.07..3,767.08 rows=10 width=19) (actual time=937.440..973.888 rows=3,895 loops=1)

4. 500.011 952.604 ↓ 23,165.4 231,654 1

Sort (cost=3,767.07..3,767.07 rows=10 width=19) (actual time=937.438..952.604 rows=231,654 loops=1)

  • Sort Key: dm.sku, dm.date DESC
  • Sort Method: quicksort Memory: 18534kB
5. 74.001 452.593 ↓ 23,165.4 231,654 1

Hash Join (cost=434.00..3,767.03 rows=10 width=19) (actual time=9.051..452.593 rows=231,654 loops=1)

  • Hash Cond: ((dm.sku)::text = (p_2.sku)::text)
6. 372.679 372.679 ↓ 3.3 294,606 1

Index Only Scan using daily_product_metrics_ongoing_totals_pkey on daily_product_metrics_ongoing_totals dm (cost=0.14..3,286.26 rows=89,354 width=47) (actual time=2.729..372.679 rows=294,606 loops=1)

  • Index Cond: ((seller_id = 'A1LMQBEOGTEVAC'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text) AND (date >= '2019-04-09 00:00:00'::timestamp without time zone) AND (date <= '2019-06-08 00:00:00'::timestamp without time zone))
  • Heap Fetches: 94411
7. 1.454 5.913 ↑ 1.2 5,198 1

Hash (cost=411.59..411.59 rows=6,362 width=44) (actual time=5.913..5.913 rows=5,198 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 430kB
8. 4.459 4.459 ↑ 1.2 5,198 1

Index Only Scan using products_seller_marketplace_sku_unique on products p_2 (cost=0.11..411.59 rows=6,362 width=44) (actual time=0.070..4.459 rows=5,198 loops=1)

  • Index Cond: ((seller_id = 'A1LMQBEOGTEVAC'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text))
  • Heap Fetches: 389
9. 90.558 10,843.611 ↓ 24,802.6 124,013 1

Sort (cost=204,589.02..204,589.02 rows=5 width=165) (actual time=10,837.070..10,843.611 rows=124,013 loops=1)

  • Sort Key: p.sku, ((date_range.date)::date)
  • Sort Method: quicksort Memory: 29840kB
10. 136.596 10,753.053 ↓ 24,802.6 124,013 1

Nested Loop Left Join (cost=159,054.60..204,589.01 rows=5 width=165) (actual time=8,603.821..10,753.053 rows=124,013 loops=1)

11. 50.714 9,500.340 ↓ 24,802.6 124,013 1

Hash Left Join (cost=159,054.46..204,568.27 rows=5 width=151) (actual time=8,603.701..9,500.340 rows=124,013 loops=1)

  • Hash Cond: ((p.sku)::text = (base_date.sku)::text)
  • Join Filter: (((p.seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((p.marketplace_id)::text = 'ATVPDKIKX0DER'::text))
12. 687.408 8,473.770 ↓ 24,802.6 124,013 1

Hash Left Join (cost=159,054.36..204,568.14 rows=5 width=147) (actual time=7,627.775..8,473.770 rows=124,013 loops=1)

  • Hash Cond: ((p.sku)::text = (dpa.sku)::text)
  • Join Filter: (dpa.date = date_range.date)
  • Rows Removed by Join Filter: 3439115
13.          

CTE profile

14. 0.005 0.093 ↑ 1.0 1 1

Limit (cost=0.12..8.13 rows=1 width=8) (actual time=0.089..0.093 rows=1 loops=1)

15. 0.003 0.088 ↑ 1.0 1 1

Nested Loop (cost=0.12..8.13 rows=1 width=8) (actual time=0.088..0.088 rows=1 loops=1)

16. 0.033 0.033 ↑ 1.0 1 1

Index Scan using seller_id_unique on merchants m (cost=0.06..4.06 rows=1 width=4) (actual time=0.033..0.033 rows=1 loops=1)

  • Index Cond: ((seller_id)::text = 'A1LMQBEOGTEVAC'::text)
17. 0.052 0.052 ↑ 1.0 1 1

Index Scan using profiles_merchant_id_index on profiles p_3 (cost=0.06..4.06 rows=1 width=16) (actual time=0.052..0.052 rows=1 loops=1)

  • Index Cond: (merchant_id = m.id)
  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 1
18.          

CTE date_range

19. 0.073 0.073 ↑ 16.4 61 1

Function Scan on generate_series (cost=0.00..3.00 rows=1,000 width=8) (actual time=0.062..0.073 rows=61 loops=1)

20. 15.311 160.547 ↓ 24,802.6 124,013 1

Nested Loop (cost=0.34..45,514.11 rows=5 width=63) (actual time=1.903..160.547 rows=124,013 loops=1)

21. 4.303 96.444 ↓ 2,033.0 2,033 1

Nested Loop Semi Join (cost=0.34..45,506.10 rows=1 width=55) (actual time=1.833..96.444 rows=2,033 loops=1)

22. 16.226 16.226 ↑ 1.2 5,061 1

Index Scan using products_seller_marketplace_isvalid_sku_id_idx on products p (cost=0.11..12,345.42 rows=6,106 width=55) (actual time=0.089..16.226 rows=5,061 loops=1)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (is_valid = true))
  • Filter: is_valid
23. 0.000 75.915 ↓ 0.0 0 5,061

Nested Loop (cost=0.23..5.43 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=5,061)

24. 0.000 0.000 ↑ 1.0 1 5,061

CTE Scan on profile prof (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=5,061)

25. 3.749 75.915 ↓ 0.0 0 5,061

Nested Loop (cost=0.23..5.42 rows=1 width=32) (actual time=0.015..0.015 rows=0 loops=5,061)

  • Join Filter: (prof.id = pf.profile_id)
26. 30.366 30.366 ↑ 1.0 1 5,061

Index Scan using product_ads_profile_sku_campaigns_idx on product_ads pa (cost=0.11..4.12 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=5,061)

  • Index Cond: ((profile_id = prof.id) AND ((sku)::text = (p.sku)::text))
27. 41.800 41.800 ↓ 0.0 0 5,225

Index Scan using daily_product_ad_performances_product_ad_id_date_pkey on daily_product_ad_performances pf (cost=0.11..1.30 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=5,225)

  • Index Cond: ((product_ad_id = pa.id) AND (date >= '2019-06-09'::date) AND (date <= '2019-08-08'::date))
  • Filter: (pa.profile_id = profile_id)
28. 48.792 48.792 ↓ 12.2 61 2,033

CTE Scan on date_range (cost=0.00..8.00 rows=5 width=8) (actual time=0.001..0.024 rows=61 loops=2,033)

  • Filter: (((date)::date >= '2019-06-09'::date) AND ((date)::date <= '2019-08-08'::date))
29. 25.109 7,625.815 ↓ 649.7 85,111 1

Hash (cost=159,042.43..159,042.43 rows=131 width=108) (actual time=7,625.815..7,625.815 rows=85,111 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7838kB
30. 13.899 7,600.706 ↓ 649.7 85,111 1

Subquery Scan on dpa (cost=159,040.99..159,042.43 rows=131 width=108) (actual time=7,451.684..7,600.706 rows=85,111 loops=1)

31. 118.200 7,586.807 ↓ 649.7 85,111 1

GroupAggregate (cost=159,040.99..159,042.04 rows=131 width=172) (actual time=7,451.680..7,586.807 rows=85,111 loops=1)

  • Group Key: pf_1.date, pa_1.sku
32. 537.056 7,468.607 ↓ 1,549.1 202,929 1

Sort (cost=159,040.99..159,041.06 rows=131 width=38) (actual time=7,451.639..7,468.607 rows=202,929 loops=1)

  • Sort Key: pf_1.date, pa_1.sku
  • Sort Method: quicksort Memory: 21998kB
33. 98.729 6,931.551 ↓ 1,549.1 202,929 1

Nested Loop (cost=1,286.85..159,040.07 rows=131 width=38) (actual time=158.897..6,931.551 rows=202,929 loops=1)

34. 10.394 203.167 ↑ 3.5 14,635 1

Nested Loop (cost=1,286.73..93,997.48 rows=50,722 width=40) (actual time=156.878..203.167 rows=14,635 loops=1)

35. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on profile p_1 (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.018 rows=1 loops=1)

36. 36.075 192.755 ↑ 3.5 14,635 1

Bitmap Heap Scan on product_ads pa_1 (cost=1,286.73..93,845.31 rows=50,722 width=32) (actual time=156.867..192.755 rows=14,635 loops=1)

  • Recheck Cond: (profile_id = p_1.id)
  • Heap Blocks: exact=364
37. 156.680 156.680 ↑ 3.5 14,635 1

Bitmap Index Scan on product_ads_profile_sku_campaigns_idx (cost=0.00..1,284.20 rows=50,722 width=0) (actual time=156.680..156.680 rows=14,635 loops=1)

  • Index Cond: (profile_id = p_1.id)
38. 6,629.655 6,629.655 ↓ 14.0 14 14,635

Index Scan using daily_product_ad_performances_product_ad_id_date_pkey on daily_product_ad_performances pf_1 (cost=0.11..1.28 rows=1 width=38) (actual time=0.376..0.453 rows=14 loops=14,635)

  • Index Cond: (product_ad_id = pa_1.id)
  • Filter: (pa_1.profile_id = profile_id)
39. 0.797 975.856 ↓ 389.5 3,895 1

Hash (cost=0.06..0.06 rows=10 width=36) (actual time=975.856..975.856 rows=3,895 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 218kB
40. 975.059 975.059 ↓ 389.5 3,895 1

CTE Scan on base_date_by_sku base_date (cost=0.00..0.06 rows=10 width=36) (actual time=937.454..975.059 rows=3,895 loops=1)

41. 1,116.117 1,116.117 ↑ 1.0 1 124,013

Index Scan using daily_product_metrics_ongoing_totals_pkey on daily_product_metrics_ongoing_totals base_row (cost=0.14..4.15 rows=1 width=65) (actual time=0.009..0.009 rows=1 loops=124,013)

  • Index Cond: (((p.seller_id)::text = (seller_id)::text) AND ((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((p.marketplace_id)::text = (marketplace_id)::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (base_date.date = date) AND ((p.sku)::text = (sku)::text))
Planning time : 11.241 ms
Execution time : 11,118.805 ms