explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QHqy : long

Settings
# exclusive inclusive rows x rows loops node
1. 3.083 5,485.699 ↑ 1.0 1 1

Aggregate (cost=441,460.78..441,460.79 rows=1 width=32) (actual time=5,485.699..5,485.699 rows=1 loops=1)

  • Buffers: shared hit=1,048 read=338,068 dirtied=31 written=30
  • JIT:
  • Functions: 62
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 34.495 ms, Inlining 0.000 ms, Optimization 26.625 ms, Emission 490.194 ms, Total 551.314 ms
2.          

CTE target

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

4.          

CTE basemix

5. 0.000 5,470.654 ↓ 180.0 180 1

GroupAggregate (cost=441,459.68..441,459.90 rows=1 width=1,399) (actual time=5,464.608..5,470.654 rows=180 loops=1)

  • Group Key: osm_fcpool_1.plnt, osm_fcpool_1.promo, osm_fcpool_1.terms, osm_fcpool_1.remit_to, osm_fcpool_1.bill_class, osm_fcpool_1.bill_cust, osm_fcpool_1.bill_rep, osm_fcpool_1.bill_terr, osm_fcpool_1.ship_class, osm_fcpool_1.ship_cust, osm_fcpool_1.ship_rep, osm_fcpool_1.ship_terr, osm_fcpool_1.quota_rep, osm_fcpool_1.account, osm_fcpool_1.shipgrp, osm_fcpool_1.geo, osm_fcpool_1.chan, osm_fcpool_1.orig_ctry, osm_fcpool_1.orig_prov, osm_fcpool_1.orig_post, osm_fcpool_1.dest_ctry, osm_fcpool_1.dest_prov, osm_fcpool_1.dest_post, osm_fcpool_1.part, osm_fcpool_1.ord_gldc, osm_fcpool_1.majg, osm_fcpool_1.ming, osm_fcpool_1.majs, osm_fcpool_1.mins, osm_fcpool_1.gldc, osm_fcpool_1.glec, osm_fcpool_1.harm, osm_fcpool_1.clss, osm_fcpool_1.brand, osm_fcpool_1.assc, osm_fcpool_1.fs_line, osm_fcpool_1.r_currency, osm_fcpool_1.r_rate, osm_fcpool_1.c_currency, osm_fcpool_1.c_rate, osm_fcpool_1.calc_status, osm_fcpool_1.flag, osm_fcpool_1.orderdate, osm_fcpool_1.requestdate, osm_fcpool_1.shipdate, osm_fcpool_1.adj_orderdate, osm_fcpool_1.adj_requestdate, osm_fcpool_1.adj_shipdate, osm_fcpool_1.order_season, osm_fcpool_1.order_month, osm_fcpool_1.ship_season, osm_fcpool_1.ship_month, osm_fcpool_1.request_season, osm_fcpool_1.request_month, osm_fcpool_1.part_descr, osm_fcpool_1.part_family, osm_fcpool_1.part_group, osm_fcpool_1.branding, osm_fcpool_1.color, osm_fcpool_1.segm, osm_fcpool_1.bill_cust_descr, osm_fcpool_1.billto_group, osm_fcpool_1.ship_cust_descr, osm_fcpool_1.shipto_group, osm_fcpool_1.majg_descr, osm_fcpool_1.ming_descr, osm_fcpool_1.majs_descr, osm_fcpool_1.mins_descr, osm_fcpool_1.mod_chan, osm_fcpool_1.mod_chansub, osm_fcpool_1.quota_rep_descr, osm_fcpool_1.director_descr
  • Buffers: shared hit=809 read=338,066
6.          

Initplan (for GroupAggregate)

7. 0.004 2,411.224 ↑ 1.0 1 1

Subquery Scan on testv (cost=211,403.14..211,403.17 rows=1 width=32) (actual time=2,411.223..2,411.224 rows=1 loops=1)

  • Buffers: shared hit=416 read=169,017
8. 0.731 2,411.220 ↑ 1.0 1 1

Aggregate (cost=211,403.14..211,403.15 rows=1 width=96) (actual time=2,411.220..2,411.220 rows=1 loops=1)

  • Buffers: shared hit=416 read=169,017
9. 2,410.489 2,410.489 ↓ 1,022.0 1,022 1

Seq Scan on osm_fcpool (cost=0.00..211,403.12 rows=1 width=276) (actual time=0.094..2,410.489 rows=1,022 loops=1)

  • Filter: ((adj_orderdate <= adj_shipdate) AND (quota_rep_descr = '12024 - JORDAN BUKOWSKI'::text) AND (billto_group = 'GRIFFIN'::text) AND (shipto_group = 'N CASERTANO FARMS'::text) AND (version = 'b20'::text) AND ((calc_status || flag) <> 'CLOSEDREMAINDER'::text) AND (iter = ANY ('{copy,"adj volume","adj price","upload volume","upload price"}'::text[])))
  • Rows Removed by Filter: 1,242,322
  • Buffers: shared hit=416 read=169,017
10. 86.256 5,464.660 ↓ 1,022.0 1,022 1

Sort (cost=230,056.51..230,056.52 rows=1 width=647) (actual time=5,464.524..5,464.660 rows=1,022 loops=1)

  • Sort Key: osm_fcpool_1.plnt, osm_fcpool_1.promo, osm_fcpool_1.terms, osm_fcpool_1.remit_to, osm_fcpool_1.bill_class, osm_fcpool_1.bill_cust, osm_fcpool_1.bill_rep, osm_fcpool_1.bill_terr, osm_fcpool_1.ship_class, osm_fcpool_1.ship_cust, osm_fcpool_1.ship_rep, osm_fcpool_1.ship_terr, osm_fcpool_1.quota_rep, osm_fcpool_1.account, osm_fcpool_1.shipgrp, osm_fcpool_1.geo, osm_fcpool_1.chan, osm_fcpool_1.orig_ctry, osm_fcpool_1.orig_prov, osm_fcpool_1.orig_post, osm_fcpool_1.dest_ctry, osm_fcpool_1.dest_prov, osm_fcpool_1.dest_post, osm_fcpool_1.part, osm_fcpool_1.ord_gldc, osm_fcpool_1.majg, osm_fcpool_1.ming, osm_fcpool_1.majs, osm_fcpool_1.mins, osm_fcpool_1.gldc, osm_fcpool_1.glec, osm_fcpool_1.harm, osm_fcpool_1.clss, osm_fcpool_1.brand, osm_fcpool_1.assc, osm_fcpool_1.fs_line, osm_fcpool_1.r_currency, osm_fcpool_1.r_rate, osm_fcpool_1.c_currency, osm_fcpool_1.c_rate, osm_fcpool_1.calc_status, osm_fcpool_1.flag, osm_fcpool_1.orderdate, osm_fcpool_1.requestdate, osm_fcpool_1.shipdate, osm_fcpool_1.adj_orderdate, osm_fcpool_1.adj_requestdate, osm_fcpool_1.adj_shipdate, osm_fcpool_1.order_season, osm_fcpool_1.order_month, osm_fcpool_1.ship_season, osm_fcpool_1.ship_month, osm_fcpool_1.request_season, osm_fcpool_1.request_month, osm_fcpool_1.part_descr, osm_fcpool_1.part_family, osm_fcpool_1.part_group, osm_fcpool_1.branding, osm_fcpool_1.color, osm_fcpool_1.segm, osm_fcpool_1.bill_cust_descr, osm_fcpool_1.ship_cust_descr, osm_fcpool_1.majg_descr, osm_fcpool_1.ming_descr, osm_fcpool_1.majs_descr, osm_fcpool_1.mins_descr, osm_fcpool_1.mod_chan, osm_fcpool_1.mod_chansub, osm_fcpool_1.director_descr
  • Sort Method: quicksort Memory: 1,062kB
  • Buffers: shared hit=809 read=338,066
11. 5,378.404 5,378.404 ↓ 1,022.0 1,022 1

Seq Scan on osm_fcpool osm_fcpool_1 (cost=0.00..230,056.50 rows=1 width=647) (actual time=2,934.829..5,378.404 rows=1,022 loops=1)

  • Filter: ((adj_orderdate <= adj_shipdate) AND (quota_rep_descr = '12024 - JORDAN BUKOWSKI'::text) AND (billto_group = 'GRIFFIN'::text) AND (shipto_group = 'N CASERTANO FARMS'::text) AND (version = 'b20'::text) AND ((calc_status || flag) <> 'CLOSEDREMAINDER'::text) AND (iter = ANY ('{copy,"adj volume","adj price","upload volume","upload price"}'::text[])) AND CASE $1 WHEN 'scale all'::text THEN true WHEN 'scale copy'::text THEN (iter = 'copy'::text) WHEN 'scale new part'::text THEN ((iterdef ->> 'type'::text) = 'new basket'::text) ELSE NULL::boolean END)
  • Rows Removed by Filter: 1,242,322
  • Buffers: shared hit=800 read=338,066
12.          

CTE ins

13. 0.000 5,480.103 ↓ 180.0 180 1

Insert on osm_fcpool osm_fcpool_2 (cost=0.60..0.73 rows=1 width=2,700) (actual time=5,474.017..5,480.103 rows=180 loops=1)

  • Buffers: shared hit=1,048 read=338,068 dirtied=31 written=30
14.          

Initplan (for Insert)

15. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on target (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1)

16. 0.338 7.149 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=7.149..7.149 rows=1 loops=1)

17. 6.811 6.811 ↓ 180.0 180 1

CTE Scan on basemix (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..6.811 rows=180 loops=1)

18. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

19. 0.171 0.205 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.205..0.205 rows=1 loops=1)

20. 0.034 0.034 ↓ 180.0 180 1

CTE Scan on basemix basemix_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.034 rows=180 loops=1)

21. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on target target_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

22. 0.175 0.207 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.207..0.207 rows=1 loops=1)

23. 0.032 0.032 ↓ 180.0 180 1

CTE Scan on basemix basemix_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.032 rows=180 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

25. 0.163 0.194 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.194..0.194 rows=1 loops=1)

26. 0.031 0.031 ↓ 180.0 180 1

CTE Scan on basemix basemix_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.031 rows=180 loops=1)

27. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

28. 0.167 0.198 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.198..0.198 rows=1 loops=1)

29. 0.031 0.031 ↓ 180.0 180 1

CTE Scan on basemix basemix_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.031 rows=180 loops=1)

30. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_5 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

31. 0.172 0.204 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.204..0.204 rows=1 loops=1)

32. 0.032 0.032 ↓ 180.0 180 1

CTE Scan on basemix basemix_5 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.032 rows=180 loops=1)

33. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on target target_6 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

34. 0.164 0.195 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.195..0.195 rows=1 loops=1)

35. 0.031 0.031 ↓ 180.0 180 1

CTE Scan on basemix basemix_6 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.031 rows=180 loops=1)

36. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_7 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

37. 0.168 0.199 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.199..0.199 rows=1 loops=1)

38. 0.031 0.031 ↓ 180.0 180 1

CTE Scan on basemix basemix_7 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.031 rows=180 loops=1)

39. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_8 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

40. 0.176 0.208 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.207..0.208 rows=1 loops=1)

41. 0.032 0.032 ↓ 180.0 180 1

CTE Scan on basemix basemix_8 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.032 rows=180 loops=1)

42. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_9 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

43. 0.170 0.202 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.202..0.202 rows=1 loops=1)

44. 0.032 0.032 ↓ 180.0 180 1

CTE Scan on basemix basemix_9 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.032 rows=180 loops=1)

45. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on target target_10 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

46. 0.169 0.201 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.201..0.201 rows=1 loops=1)

47. 0.032 0.032 ↓ 180.0 180 1

CTE Scan on basemix basemix_10 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.032 rows=180 loops=1)

48. 5,477.691 5,477.691 ↓ 180.0 180 1

CTE Scan on basemix b (cost=0.00..0.12 rows=1 width=2,700) (actual time=5,473.861..5,477.691 rows=180 loops=1)

  • Buffers: shared hit=809 read=338,066
49. 0.206 5,482.616 ↓ 160.0 160 1

Subquery Scan on insagg (cost=0.10..0.13 rows=1 width=848) (actual time=5,482.165..5,482.616 rows=160 loops=1)

  • Buffers: shared hit=1,048 read=338,068 dirtied=31 written=30
50. 1.362 5,482.410 ↓ 160.0 160 1

HashAggregate (cost=0.10..0.12 rows=1 width=936) (actual time=5,482.140..5,482.410 rows=160 loops=1)

  • Group Key: ins.bill_cust_descr, ins.billto_group, ins.ship_cust_descr, ins.shipto_group, ins.quota_rep_descr, ins.director_descr, ins.segm, ins.mod_chan, ins.mod_chansub, ins.majg_descr, ins.ming_descr, ins.majs_descr, ins.mins_descr, ins.brand, ins.part_family, ins.part_group, ins.branding, ins.color, ins.part_descr, ins.order_season, ins.order_month, ins.ship_season, ins.ship_month, ins.request_season, ins.request_month, ins.promo, ins.version, ins.iter
  • Buffers: shared hit=1,048 read=338,068 dirtied=31 written=30
51. 5,481.048 5,481.048 ↓ 180.0 180 1

CTE Scan on ins (cost=0.00..0.02 rows=1 width=916) (actual time=5,474.026..5,481.048 rows=180 loops=1)

  • Buffers: shared hit=1,048 read=338,068 dirtied=31 written=30