explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YGam : Optimization for: plan #AzA

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.078 8,006.724 ↓ 15.4 77 1

GroupAggregate (cost=82.53..82.75 rows=5 width=76) (actual time=8,006.644..8,006.724 rows=77 loops=1)

  • Group Key: resultset02.paymentdate, resultset02.woid
2. 0.356 8,006.646 ↓ 28.0 140 1

Sort (cost=82.53..82.54 rows=5 width=48) (actual time=8,006.636..8,006.646 rows=140 loops=1)

  • Sort Key: resultset02.paymentdate, resultset02.woid
  • Sort Method: quicksort Memory: 35kB
3. 0.027 8,006.290 ↓ 28.0 140 1

Subquery Scan on resultset02 (cost=82.37..82.47 rows=5 width=48) (actual time=8,006.225..8,006.290 rows=140 loops=1)

4. 0.139 8,006.263 ↓ 28.0 140 1

HashAggregate (cost=82.37..82.42 rows=5 width=80) (actual time=8,006.224..8,006.263 rows=140 loops=1)

  • Group Key: reminder.woid, reminder.paymentdate, reminder.total, reminder.servicecategory, reminder.priority
5.          

CTE datapool_domain

6. 1,793.116 1,793.116 ↓ 106.0 106 1

Function Scan on dblink r (cost=0.00..25.00 rows=1 width=144) (actual time=1,779.749..1,793.116 rows=106 loops=1)

  • Filter: (((ahassid)::text = '02638'::text) AND ((wolastknownstate)::text = 'COMPLETE'::text) AND (paymentdate >= to_date('2020-01-31'::text, 'YYYY-MM-DD'::text)) AND (paymentdate <= to_date('2020-01-31'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 89,229
7.          

CTE reminder

8. 0.007 3,693.823 ↑ 1.0 1 1

GroupAggregate (cost=12.59..12.62 rows=1 width=80) (actual time=3,693.823..3,693.823 rows=1 loops=1)

  • Group Key: dp.woid, dp.paymentdate
9. 0.006 3,693.816 ↑ 1.0 1 1

Sort (cost=12.59..12.60 rows=1 width=48) (actual time=3,693.815..3,693.816 rows=1 loops=1)

  • Sort Key: dp.woid, dp.paymentdate
  • Sort Method: quicksort Memory: 25kB
10. 288.912 3,693.810 ↑ 1.0 1 1

Nested Loop (cost=0.00..12.58 rows=1 width=48) (actual time=3,436.586..3,693.810 rows=1 loops=1)

  • Join Filter: ((dp.woid)::text = (r_1.woid)::text)
  • Rows Removed by Join Filter: 634,621
11. 1,793.274 1,793.274 ↓ 106.0 106 1

CTE Scan on datapool_domain dp (cost=0.00..0.02 rows=1 width=48) (actual time=1,779.751..1,793.274 rows=106 loops=1)

12. 1,611.624 1,611.624 ↓ 1,197.4 5,987 106

Function Scan on dblink r_1 (cost=0.00..12.50 rows=5 width=516) (actual time=7.712..15.204 rows=5,987 loops=106)

  • Filter: ((alasandatang)::text = 'SMS/Phone Reminder'::text)
  • Rows Removed by Filter: 59,761
13.          

CTE visit

14. 0.001 0.028 ↓ 0.0 0 1

HashAggregate (cost=0.05..0.06 rows=1 width=80) (actual time=0.028..0.028 rows=0 loops=1)

  • Group Key: dp_1.woid, dp_1.paymentdate
15. 0.027 0.027 ↓ 0.0 0 1

CTE Scan on datapool_domain dp_1 (cost=0.00..0.04 rows=1 width=48) (actual time=0.027..0.027 rows=0 loops=1)

  • Filter: (("left"((woid)::text, 3) = 'VST'::text) AND (NOT (SubPlan 3)))
  • Rows Removed by Filter: 106
16.          

SubPlan (for CTE Scan)

17. 0.000 0.000 ↓ 0.0 0

CTE Scan on reminder reminder_1 (cost=0.00..0.02 rows=1 width=32) (never executed)

18.          

CTE eventlist

19. 0.063 851.045 ↓ 62.0 62 1

GroupAggregate (cost=17.65..17.67 rows=1 width=80) (actual time=850.988..851.045 rows=62 loops=1)

  • Group Key: dp_2.woid, dp_2.paymentdate
20. 0.236 850.982 ↓ 91.0 91 1

Sort (cost=17.65..17.65 rows=1 width=48) (actual time=850.975..850.982 rows=91 loops=1)

  • Sort Key: dp_2.woid, dp_2.paymentdate
  • Sort Method: quicksort Memory: 32kB
21. 7.544 850.746 ↓ 91.0 91 1

Hash Join (cost=0.04..17.64 rows=1 width=48) (actual time=820.560..850.746 rows=91 loops=1)

  • Hash Cond: ((r_2.woid)::text = (dp_2.woid)::text)
  • Join Filter: (((r_2.typejasa)::text = 'PACKAGE'::text) OR ((r_2.typejasa)::text = 'KM,PACKAGE'::text) OR (((r_2.typejasa)::text = 'PACKAGE,KM'::text) AND ((NOT (SubPlan 5)) OR (NOT (SubPlan 6)))))
22. 843.144 843.144 ↓ 2,272.2 34,083 1

Function Scan on dblink r_2 (cost=0.00..17.50 rows=15 width=1,032) (actual time=819.123..843.144 rows=34,083 loops=1)

  • Filter: (((typejasa)::text = 'PACKAGE'::text) OR ((typejasa)::text = 'KM,PACKAGE'::text) OR ((typejasa)::text = 'PACKAGE,KM'::text))
  • Rows Removed by Filter: 31,665
23. 0.027 0.058 ↓ 106.0 106 1

Hash (cost=0.02..0.02 rows=1 width=48) (actual time=0.058..0.058 rows=106 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
24. 0.031 0.031 ↓ 106.0 106 1

CTE Scan on datapool_domain dp_2 (cost=0.00..0.02 rows=1 width=48) (actual time=0.004..0.031 rows=106 loops=1)

25.          

SubPlan (for Hash Join)

26. 0.000 0.000 ↓ 0.0 0

CTE Scan on reminder reminder_2 (cost=0.00..0.02 rows=1 width=32) (never executed)

27. 0.000 0.000 ↓ 0.0 0

CTE Scan on visit visit_1 (cost=0.00..0.02 rows=1 width=32) (never executed)

28.          

CTE express

29. 0.001 2,608.640 ↓ 0.0 0 1

GroupAggregate (cost=12.64..12.66 rows=1 width=80) (actual time=2,608.640..2,608.640 rows=0 loops=1)

  • Group Key: dp_3.woid, dp_3.paymentdate
30. 0.011 2,608.639 ↓ 0.0 0 1

Sort (cost=12.64..12.64 rows=1 width=48) (actual time=2,608.638..2,608.639 rows=0 loops=1)

  • Sort Key: dp_3.woid, dp_3.paymentdate
  • Sort Method: quicksort Memory: 25kB
31. 187.770 2,608.628 ↓ 0.0 0 1

Nested Loop (cost=0.00..12.63 rows=1 width=48) (actual time=2,608.628..2,608.628 rows=0 loops=1)

  • Join Filter: ((dp_3.woid)::text = (r_3.woid)::text)
  • Rows Removed by Join Filter: 606,426
32. 0.772 0.878 ↓ 106.0 106 1

CTE Scan on datapool_domain dp_3 (cost=0.00..0.06 rows=1 width=48) (actual time=0.010..0.878 rows=106 loops=1)

  • Filter: ((NOT (SubPlan 8)) OR (NOT (SubPlan 9)) OR (NOT (SubPlan 10)))
33.          

SubPlan (for CTE Scan)

34. 0.106 0.106 ↑ 1.0 1 106

CTE Scan on reminder reminder_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=106)

35. 0.000 0.000 ↓ 0.0 0 106

CTE Scan on visit visit_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=106)

36. 0.000 0.000 ↓ 0.0 0

CTE Scan on eventlist eventlist_1 (cost=0.00..0.02 rows=1 width=32) (never executed)

37. 2,419.980 2,419.980 ↓ 1,144.2 5,721 106

Function Scan on dblink r_3 (cost=0.00..12.50 rows=5 width=516) (actual time=7.701..22.830 rows=5,721 loops=106)

  • Filter: ((pittype)::text = 'EXPRESS'::text)
  • Rows Removed by Filter: 60,027
38.          

CTE reguler

39. 0.074 852.391 ↓ 77.0 77 1

GroupAggregate (cost=13.95..14.04 rows=1 width=80) (actual time=852.322..852.391 rows=77 loops=1)

  • Group Key: dp_4.woid, dp_4.paymentdate
40. 0.309 852.317 ↓ 21.2 106 1

Sort (cost=13.95..13.96 rows=5 width=48) (actual time=852.308..852.317 rows=106 loops=1)

  • Sort Key: dp_4.woid, dp_4.paymentdate
  • Sort Method: quicksort Memory: 33kB
41. 27.115 852.008 ↓ 21.2 106 1

Hash Join (cost=0.09..13.89 rows=5 width=48) (actual time=819.875..852.008 rows=106 loops=1)

  • Hash Cond: ((r_4.woid)::text = (dp_4.woid)::text)
42. 824.748 824.748 ↓ 65.7 65,748 1

Function Scan on dblink r_4 (cost=0.00..10.00 rows=1,000 width=516) (actual time=818.211..824.748 rows=65,748 loops=1)

43. 0.028 0.145 ↓ 106.0 106 1

Hash (cost=0.08..0.08 rows=1 width=48) (actual time=0.144..0.145 rows=106 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
44. 0.117 0.117 ↓ 106.0 106 1

CTE Scan on datapool_domain dp_4 (cost=0.00..0.08 rows=1 width=48) (actual time=0.016..0.117 rows=106 loops=1)

  • Filter: ((NOT (SubPlan 12)) OR (NOT (SubPlan 13)) OR (NOT (SubPlan 14)) OR ((NOT (SubPlan 15)) AND ((wolastknownstate)::text = 'COMPLETE'::text)))
45.          

SubPlan (for CTE Scan)

46. 0.000 0.000 ↑ 1.0 1 106

CTE Scan on reminder reminder_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=106)

47. 0.000 0.000 ↓ 0.0 0 106

CTE Scan on visit visit_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=106)

48. 0.000 0.000 ↓ 0.0 0

CTE Scan on eventlist eventlist_2 (cost=0.00..0.02 rows=1 width=32) (never executed)

49. 0.000 0.000 ↓ 0.0 0

CTE Scan on express express_1 (cost=0.00..0.02 rows=1 width=32) (never executed)

50. 0.026 8,006.124 ↓ 28.0 140 1

Append (cost=0.00..0.25 rows=5 width=80) (actual time=3,693.832..8,006.124 rows=140 loops=1)

51. 3,693.832 3,693.832 ↑ 1.0 1 1

CTE Scan on reminder (cost=0.00..0.04 rows=1 width=80) (actual time=3,693.832..3,693.832 rows=1 loops=1)

  • Filter: ((date_part('month'::text, (paymentdate)::timestamp without time zone) = '1'::double precision) AND (date_part('year'::text, (paymentdate)::timestamp without time zone) = '2020'::double precision))
52. 0.029 0.029 ↓ 0.0 0 1

CTE Scan on visit (cost=0.00..0.04 rows=1 width=80) (actual time=0.029..0.029 rows=0 loops=1)

  • Filter: ((date_part('month'::text, (paymentdate)::timestamp without time zone) = '1'::double precision) AND (date_part('year'::text, (paymentdate)::timestamp without time zone) = '2020'::double precision))
53. 851.116 851.116 ↓ 62.0 62 1

CTE Scan on eventlist (cost=0.00..0.04 rows=1 width=80) (actual time=850.996..851.116 rows=62 loops=1)

  • Filter: ((date_part('month'::text, (paymentdate)::timestamp without time zone) = '1'::double precision) AND (date_part('year'::text, (paymentdate)::timestamp without time zone) = '2020'::double precision))
54. 2,608.642 2,608.642 ↓ 0.0 0 1

CTE Scan on express (cost=0.00..0.04 rows=1 width=80) (actual time=2,608.642..2,608.642 rows=0 loops=1)

  • Filter: ((date_part('month'::text, (paymentdate)::timestamp without time zone) = '1'::double precision) AND (date_part('year'::text, (paymentdate)::timestamp without time zone) = '2020'::double precision))
55. 852.479 852.479 ↓ 77.0 77 1

CTE Scan on reguler (cost=0.00..0.04 rows=1 width=80) (actual time=852.331..852.479 rows=77 loops=1)

  • Filter: ((date_part('month'::text, (paymentdate)::timestamp without time zone) = '1'::double precision) AND (date_part('year'::text, (paymentdate)::timestamp without time zone) = '2020'::double precision))
Planning time : 1.693 ms
Execution time : 8,064.000 ms