explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WtSr

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Subquery Scan on sb3 (cost=4,512,821.45..4,906,052.55 rows=2,154,691 width=1,391) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=4,512,821.45..4,728,290.55 rows=2,154,691 width=1,108) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=4,512,821.45..4,518,208.17 rows=2,154,691 width=833) (actual rows= loops=)

  • Sort Key: sb1.campaign_id, (CASE WHEN ((lower(sb1.aasm_state) = 'approved'::text) OR (lower(sb1.status_name) = 'planning'::text)) THEN sb1.media_plan_end_date ELSE NULL::date END) DESC NULLS LAST
4. 0.000 0.000 ↓ 0.0

WindowAgg (cost=4,178,423.64..4,286,158.19 rows=2,154,691 width=833) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=4,178,423.64..4,183,810.37 rows=2,154,691 width=829) (actual rows= loops=)

  • Sort Key: sb1.campaign_id, (CASE WHEN ((lower(sb1.aasm_state) = 'approved'::text) OR (lower(sb1.status_name) = 'planning'::text)) THEN sb1.media_plan_start_date ELSE NULL::date END) DESC NULLS LAST
6. 0.000 0.000 ↓ 0.0

Subquery Scan on sb1 (cost=3,725,517.83..3,951,760.39 rows=2,154,691 width=829) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,725,517.83..3,887,119.66 rows=2,154,691 width=895) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Sort (cost=3,725,517.83..3,730,904.56 rows=2,154,691 width=765) (actual rows= loops=)

  • Sort Key: mp.campaign_id
9. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,455,760.76..3,498,854.58 rows=2,154,691 width=765) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=3,455,760.76..3,461,147.49 rows=2,154,691 width=757) (actual rows= loops=)

  • Sort Key: li.media_plan_id
11. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,186,003.69..3,229,097.51 rows=2,154,691 width=757) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Sort (cost=3,186,003.69..3,191,390.41 rows=2,154,691 width=749) (actual rows= loops=)

  • Sort Key: li.tactic_id
13. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,875,253.12..2,959,340.43 rows=2,154,691 width=749) (actual rows= loops=)

  • Hash Cond: (lif.line_item_id = li.id)
14. 0.000 0.000 ↓ 0.0

HashAggregate (cost=444,195.00..469,211.16 rows=2,501,616 width=106) (actual rows= loops=)

  • Group Key: lif.line_item_id, string_agg(DISTINCT lower(f.description), '~'::text ORDER BY (lower(f.description)))
15. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=375,359.33..431,686.92 rows=2,501,616 width=106) (actual rows= loops=)

  • Group Key: lif.line_item_id
16. 0.000 0.000 ↓ 0.0

Sort (cost=375,359.33..381,623.67 rows=2,505,739 width=44) (actual rows= loops=)

  • Sort Key: lif.line_item_id
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.11..109,039.30 rows=2,505,739 width=44) (actual rows= loops=)

  • Hash Cond: (lif.format_id = f.id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on line_items_formats lif (cost=0.00..74,584.28 rows=2,505,739 width=74) (actual rows= loops=)

  • Filter: (NOT is_deleted)
19. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=44) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on formats f (cost=0.00..1.05 rows=5 width=44) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=2,408,030.19..2,408,030.19 rows=1,842,235 width=717) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,329,770.71..2,408,030.19 rows=1,842,235 width=717) (actual rows= loops=)

  • Hash Cond: (li.id = lida.id)
23. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,322,815.64..2,392,767.93 rows=1,842,235 width=701) (actual rows= loops=)

  • Hash Cond: (line_items_platforms.line_item_id = li.id)
24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,008,197.14..1,029,008.10 rows=2,081,096 width=69) (actual rows= loops=)

  • Group Key: line_items_platforms.line_item_id, string_agg(DISTINCT initcap(pf.name), '~'::text ORDER BY (initcap(pf.name)))
25. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=913,179.93..997,791.66 rows=2,081,096 width=69) (actual rows= loops=)

  • Group Key: line_items_platforms.line_item_id
26. 0.000 0.000 ↓ 0.0

Sort (cost=913,179.93..927,829.44 rows=5,859,803 width=45) (actual rows= loops=)

  • Sort Key: line_items_platforms.line_item_id
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.09..254,467.15 rows=5,859,803 width=45) (actual rows= loops=)

  • Hash Cond: (line_items_platforms.platform_id = pf.id)
28. 0.000 0.000 ↓ 0.0

Seq Scan on line_items_platforms (cost=0.00..173,893.77 rows=5,859,803 width=74) (actual rows= loops=)

  • Filter: (NOT is_deleted)
29. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=45) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on platforms pf (cost=0.00..1.04 rows=4 width=45) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=1,291,590.56..1,291,590.56 rows=1,842,235 width=669) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,224,750.37..1,291,590.56 rows=1,842,235 width=669) (actual rows= loops=)

  • Hash Cond: (li.media_plan_id = mp.id)
33. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,205,753.70..1,246,150.81 rows=2,138,852 width=390) (actual rows= loops=)

  • Hash Cond: (line_items_dimensions.line_item_id = li.id)
34. 0.000 0.000 ↓ 0.0

HashAggregate (cost=924,161.00..935,703.03 rows=1,154,203 width=69) (actual rows= loops=)

  • Group Key: line_items_dimensions.line_item_id, string_agg(DISTINCT lower(d.description), '~'::text ORDER BY (lower(d.description)))
35. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=849,262.29..918,389.99 rows=1,154,203 width=69) (actual rows= loops=)

  • Group Key: line_items_dimensions.line_item_id
36. 0.000 0.000 ↓ 0.0

Sort (cost=849,262.29..862,937.33 rows=5,470,016 width=44) (actual rows= loops=)

  • Sort Key: line_items_dimensions.line_item_id
37. 0.000 0.000 ↓ 0.0

Hash Join (cost=848.17..237,082.34 rows=5,470,016 width=44) (actual rows= loops=)

  • Hash Cond: (line_items_dimensions.dimension_id = d.id)
38. 0.000 0.000 ↓ 0.0

Seq Scan on line_items_dimensions (cost=0.00..161,021.45 rows=5,470,016 width=74) (actual rows= loops=)

  • Filter: (NOT is_deleted)
39. 0.000 0.000 ↓ 0.0

Hash (cost=652.52..652.52 rows=15,652 width=44) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on dimensions d (cost=0.00..652.52 rows=15,652 width=44) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=254,857.05..254,857.05 rows=2,138,852 width=358) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Append (cost=0.00..254,857.05 rows=2,138,852 width=358) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on line_items li (cost=0.00..0.00 rows=1 width=1,364) (actual rows= loops=)

  • Filter: (NOT is_deleted)
44. 0.000 0.000 ↓ 0.0

Seq Scan on line_items_direct li_1 (cost=0.00..245,691.32 rows=2,043,664 width=353) (actual rows= loops=)

  • Filter: (NOT is_deleted)
45. 0.000 0.000 ↓ 0.0

Seq Scan on line_items_dsp li_2 (cost=0.00..9,165.73 rows=95,187 width=462) (actual rows= loops=)

  • Filter: (NOT is_deleted)
46. 0.000 0.000 ↓ 0.0

Hash (cost=16,438.32..16,438.32 rows=204,668 width=279) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,798.35..16,438.32 rows=204,668 width=279) (actual rows= loops=)

  • Hash Cond: (mp.campaign_id = c.id)
48. 0.000 0.000 ↓ 0.0

Seq Scan on media_plans mp (cost=0.00..11,702.21 rows=237,621 width=106) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash (cost=1,515.91..1,515.91 rows=22,595 width=173) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on campaigns c (cost=0.00..1,515.91 rows=22,595 width=173) (actual rows= loops=)

  • Filter: (agency_id = 'dfc2ea45-cfee-42a0-a629-8be9270dd74c'::bpchar)
51. 0.000 0.000 ↓ 0.0

Hash (cost=4,925.03..4,925.03 rows=162,403 width=53) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on line_item_dates lida (cost=0.00..4,925.03 rows=162,403 width=53) (actual rows= loops=)