explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jxt9

Settings
# exclusive inclusive rows x rows loops node
1. 1,908.609 1,908.609 ↓ 1,372.7 4,118 1

CTE Scan on collation_data (cost=3,387.90..3,388.00 rows=3 width=32) (actual time=1,835.800..1,908.609 rows=4,118 loops=1)

2.          

CTE seg_data

3. 12.379 77.871 ↓ 9,903.0 9,903 1

WindowAgg (cost=3,387.26..3,387.29 rows=1 width=179) (actual time=63.771..77.871 rows=9,903 loops=1)

4. 20.838 65.492 ↓ 9,903.0 9,903 1

Sort (cost=3,387.26..3,387.27 rows=1 width=171) (actual time=63.765..65.492 rows=9,903 loops=1)

  • Sort Key: a.prop_code, s.create_date, s.res_confo_num, s.asof_date
  • Sort Method: quicksort Memory: 2,963kB
5. 5.309 44.654 ↓ 9,903.0 9,903 1

Nested Loop (cost=3,379.19..3,387.25 rows=1 width=171) (actual time=16.567..44.654 rows=9,903 loops=1)

6. 0.756 17.810 ↓ 4,307.0 4,307 1

Unique (cost=3,378.63..3,378.64 rows=1 width=103) (actual time=16.552..17.810 rows=4,307 loops=1)

7.          

CTE b

8. 1.744 1.744 ↓ 1.4 2,494 1

Index Scan using mrdw_res_data_prod_20191204_n_prop_code_asof_date_last_upd_idx1 on mrdw_res_data_prod_20191204_nycmq (cost=0.43..3,260.11 rows=1,831 width=34) (actual time=0.040..1.744 rows=2,494 loops=1)

  • Index Cond: (((prop_code)::text = 'NYCMQ'::text) AND (asof_date = '2019-12-04 00:00:00'::timestamp without time zone))
9. 3.049 17.054 ↓ 4,331.0 4,331 1

Sort (cost=118.52..118.52 rows=1 width=103) (actual time=16.551..17.054 rows=4,331 loops=1)

  • Sort Key: a.res_confo_num, a.create_date, a.asof_date, a.last_upd_ts DESC
  • Sort Method: quicksort Memory: 802kB
10. 1.280 14.005 ↓ 4,331.0 4,331 1

Nested Loop (cost=0.56..118.51 rows=1 width=103) (actual time=0.056..14.005 rows=4,331 loops=1)

11. 2.749 2.749 ↓ 277.1 2,494 1

CTE Scan on b (cost=0.00..41.20 rows=9 width=52) (actual time=0.044..2.749 rows=2,494 loops=1)

  • Filter: ((prop_code)::text = 'NYCMQ'::text)
12. 9.976 9.976 ↓ 2.0 2 2,494

Index Scan using mrd_nycmq_idx_2 on mrdw_res_data_prod_20191204_nycmq a (cost=0.56..8.58 rows=1 width=95) (actual time=0.003..0.004 rows=2 loops=2,494)

  • Index Cond: ((res_confo_num = b.res_confo_num) AND (create_date = b.create_date) AND (asof_date <= '2019-12-04 00:00:00'::timestamp without time zone) AND ((prop_code)::text = 'NYCMQ'::text))
13. 21.535 21.535 ↓ 2.0 2 4,307

Index Scan using mrdw_seg_data_prod_20191204_n_prop_code_asof_date_last_upd_idx1 on mrdw_seg_data_prod_20191204_nycmq s (cost=0.56..8.59 rows=1 width=102) (actual time=0.005..0.005 rows=2 loops=4,307)

  • Index Cond: (((prop_code)::text = (a.prop_code)::text) AND (asof_date = a.asof_date) AND (asof_date <= '2019-12-04 00:00:00'::timestamp without time zone) AND (last_upd_ts = a.last_upd_ts) AND (res_confo_num = a.res_confo_num) AND (create_date = a.create_date))
14.          

CTE agg_data

15. 8.645 404.095 ↓ 4,307.0 4,307 1

WindowAgg (cost=0.25..0.30 rows=1 width=476) (actual time=386.569..404.095 rows=4,307 loops=1)

16. 6.421 395.450 ↓ 4,307.0 4,307 1

WindowAgg (cost=0.25..0.28 rows=1 width=468) (actual time=386.517..395.450 rows=4,307 loops=1)

17. 21.207 389.029 ↓ 4,307.0 4,307 1

Sort (cost=0.25..0.25 rows=1 width=436) (actual time=386.505..389.029 rows=4,307 loops=1)

  • Sort Key: b_1.prop_code, b_1.create_date, b_1.res_confo_num, b_1.day_rank
  • Sort Method: external merge Disk: 6,096kB
18. 0.815 367.822 ↓ 4,307.0 4,307 1

Subquery Scan on b_1 (cost=0.14..0.24 rows=1 width=436) (actual time=293.414..367.822 rows=4,307 loops=1)

19. 280.212 367.007 ↓ 4,307.0 4,307 1

HashAggregate (cost=0.14..0.23 rows=1 width=604) (actual time=293.413..367.007 rows=4,307 loops=1)

  • Group Key: a_1.prop_code, a_1.res_confo_num, a_1.cur_asof_date, a_1.cur_past_asof_date, a_1.last_upd_ts, a_1.create_date, a_1.currency_code, a_1.res_status, a_1.brand_cd, a_1.brand_txt, a_1.prop_name, a_1.arvl_date, a_1.dep_date, a_1.duration, a_1.day_rank
20. 86.795 86.795 ↓ 9,903.0 9,903 1

CTE Scan on seg_data a_1 (cost=0.00..0.02 rows=1 width=522) (actual time=63.775..86.795 rows=9,903 loops=1)

21.          

CTE current_record

22. 415.594 415.594 ↓ 2,486.0 2,486 1

CTE Scan on agg_data a_2 (cost=0.00..0.02 rows=1 width=508) (actual time=386.581..415.594 rows=2,486 loops=1)

  • Filter: (cur_past_asof_date = cur_asof_date)
  • Rows Removed by Filter: 1,821
23.          

CTE previous_record

24. 3.904 3.904 ↓ 1,362.0 1,362 1

CTE Scan on agg_data a_3 (cost=0.00..0.03 rows=1 width=508) (actual time=0.016..3.904 rows=1,362 loops=1)

  • Filter: (day_rank = (max_rank - 1))
  • Rows Removed by Filter: 2,945
25.          

CTE collation_data

26. 13.433 1,838.941 ↓ 1,372.7 4,118 1

Sort (cost=0.25..0.26 rows=3 width=264) (actual time=1,835.730..1,838.941 rows=4,118 loops=1)

  • Sort Key: (CASE a_4.rec_type WHEN 'first/upsell'::text THEN 3 WHEN 'previous'::text THEN 2 WHEN 'current'::text THEN 1 ELSE NULL::integer END)
  • Sort Method: external sort Disk: 5,496kB
27. 1.555 1,825.508 ↓ 1,372.7 4,118 1

Result (cost=0.00..0.23 rows=3 width=264) (actual time=386.590..1,825.508 rows=4,118 loops=1)

28. 0.407 1,823.953 ↓ 1,372.7 4,118 1

Append (cost=0.00..0.18 rows=3 width=292) (actual time=386.584..1,823.953 rows=4,118 loops=1)

29. 423.773 423.773 ↓ 2,486.0 2,486 1

CTE Scan on current_record a_4 (cost=0.00..0.02 rows=1 width=292) (actual time=386.583..423.773 rows=2,486 loops=1)

30. 5.798 5.798 ↓ 1,362.0 1,362 1

CTE Scan on previous_record a_5 (cost=0.00..0.02 rows=1 width=292) (actual time=0.019..5.798 rows=1,362 loops=1)

31. 0.160 1,393.975 ↓ 270.0 270 1

Subquery Scan on a_6 (cost=0.09..0.13 rows=1 width=292) (actual time=1,392.018..1,393.975 rows=270 loops=1)

  • Filter: ((a_6.max_rank_minus_max <> a_6.prev_rec_day_rank) AND (a_6.max_rank_minus_max = a_6.day_rank))
  • Rows Removed by Filter: 1,095
32. 1.413 1,393.815 ↓ 1,365.0 1,365 1

WindowAgg (cost=0.09..0.11 rows=1 width=492) (actual time=1,392.005..1,393.815 rows=1,365 loops=1)

33. 3.084 1,392.402 ↓ 1,365.0 1,365 1

Sort (cost=0.09..0.09 rows=1 width=320) (actual time=1,391.994..1,392.402 rows=1,365 loops=1)

  • Sort Key: a_7.prop_code, a_7.res_confo_num, a_7.create_date
  • Sort Method: quicksort Memory: 2,663kB
34. 735.693 1,389.318 ↓ 1,365.0 1,365 1

Nested Loop (cost=0.00..0.08 rows=1 width=320) (actual time=0.972..1,389.318 rows=1,365 loops=1)

  • Join Filter: ((a_7.day_rank <= b_2.day_rank) AND ((a_7.prop_code)::text = (b_2.prop_code)::text) AND (a_7.res_confo_num = b_2.res_confo_num) AND (a_7.create_date = b_2.create_date) AND (a_7.gross_rev = b_2.gross_rev) AND (a_7.net_rev = b_2.net_rev) AND (a_7.los = b_2.los))
  • Rows Removed by Join Filter: 3,748,221
35. 6.670 6.670 ↓ 2,753.0 2,753 1

CTE Scan on agg_data a_7 (cost=0.00..0.03 rows=1 width=380) (actual time=0.017..6.670 rows=2,753 loops=1)

  • Filter: (prev_version_lag_amt <> ((gross_rev + net_rev) + (los)::numeric))
  • Rows Removed by Filter: 1,554
36. 646.955 646.955 ↓ 1,362.0 1,362 2,753

CTE Scan on previous_record b_2 (cost=0.00..0.02 rows=1 width=120) (actual time=0.000..0.235 rows=1,362 loops=2,753)