explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AzA

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.835 80,130.045 ↓ 393.6 1,968 1

GroupAggregate (cost=87.53..87.75 rows=5 width=76) (actual time=80,127.952..80,130.045 rows=1,968 loops=1)

  • Group Key: resultset02.paymentdate, resultset02.woid
2. 9.100 80,128.210 ↓ 703.0 3,515 1

Sort (cost=87.53..87.54 rows=5 width=48) (actual time=80,127.942..80,128.210 rows=3,515 loops=1)

  • Sort Key: resultset02.paymentdate, resultset02.woid
  • Sort Method: quicksort Memory: 371kB
3. 0.656 80,119.110 ↓ 703.0 3,515 1

Subquery Scan on resultset02 (cost=87.37..87.47 rows=5 width=48) (actual time=80,117.375..80,119.110 rows=3,515 loops=1)

4. 3.156 80,118.454 ↓ 703.0 3,515 1

HashAggregate (cost=87.37..87.42 rows=5 width=80) (actual time=80,117.374..80,118.454 rows=3,515 loops=1)

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

CTE datapool_domain

6. 1,801.357 1,801.357 ↓ 2,564.0 2,564 1

Function Scan on dblink r (cost=0.00..30.00 rows=1 width=144) (actual time=1,765.808..1,801.357 rows=2,564 loops=1)

  • Filter: (((ahassid)::text = '02638'::text) AND ((wolastknownstate)::text = 'COMPLETE'::text) AND (to_char((paymentdate)::timestamp with time zone, 'MM'::text) = '01'::text) AND (to_char((paymentdate)::timestamp with time zone, 'YYYY'::text) = '2020'::text))
  • Rows Removed by Filter: 86,741
7.          

CTE reminder

8. 0.018 32,419.323 ↓ 11.0 11 1

GroupAggregate (cost=12.59..12.62 rows=1 width=80) (actual time=32,419.314..32,419.323 rows=11 loops=1)

  • Group Key: dp.woid, dp.paymentdate
9. 0.061 32,419.305 ↓ 12.0 12 1

Sort (cost=12.59..12.60 rows=1 width=48) (actual time=32,419.304..32,419.305 rows=12 loops=1)

  • Sort Key: dp.woid, dp.paymentdate
  • Sort Method: quicksort Memory: 25kB
10. 7,583.834 32,419.244 ↓ 12.0 12 1

Nested Loop (cost=0.00..12.58 rows=1 width=48) (actual time=4,302.482..32,419.244 rows=12 loops=1)

  • Join Filter: ((dp.woid)::text = (r_1.woid)::text)
  • Rows Removed by Join Filter: 15,350,656
11. 1,805.562 1,805.562 ↓ 2,564.0 2,564 1

CTE Scan on datapool_domain dp (cost=0.00..0.02 rows=1 width=48) (actual time=1,765.812..1,805.562 rows=2,564 loops=1)

12. 23,029.848 23,029.848 ↓ 1,197.4 5,987 2,564

Function Scan on dblink r_1 (cost=0.00..12.50 rows=5 width=516) (actual time=0.324..8.982 rows=5,987 loops=2,564)

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

CTE visit

14. 0.001 0.534 ↓ 0.0 0 1

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

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

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

  • Filter: (("left"((woid)::text, 3) = 'VST'::text) AND (NOT (SubPlan 3)))
  • Rows Removed by Filter: 2,564
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. 1.227 861.881 ↓ 1,531.0 1,531 1

GroupAggregate (cost=17.65..17.67 rows=1 width=80) (actual time=860.493..861.881 rows=1,531 loops=1)

  • Group Key: dp_2.woid, dp_2.paymentdate
20. 7.943 860.654 ↓ 2,114.0 2,114 1

Sort (cost=17.65..17.65 rows=1 width=48) (actual time=860.483..860.654 rows=2,114 loops=1)

  • Sort Key: dp_2.woid, dp_2.paymentdate
  • Sort Method: quicksort Memory: 262kB
21. 9.536 852.711 ↓ 2,114.0 2,114 1

Hash Join (cost=0.04..17.64 rows=1 width=48) (actual time=817.959..852.711 rows=2,114 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. 842.026 842.026 ↓ 2,271.5 34,072 1

Function Scan on dblink r_2 (cost=0.00..17.50 rows=15 width=1,032) (actual time=816.729..842.026 rows=34,072 loops=1)

  • Filter: (((typejasa)::text = 'PACKAGE'::text) OR ((typejasa)::text = 'KM,PACKAGE'::text) OR ((typejasa)::text = 'PACKAGE,KM'::text))
  • Rows Removed by Filter: 31,658
23. 0.588 1.149 ↓ 2,564.0 2,564 1

Hash (cost=0.02..0.02 rows=1 width=48) (actual time=1.148..1.149 rows=2,564 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 203kB
24. 0.561 0.561 ↓ 2,564.0 2,564 1

CTE Scan on datapool_domain dp_2 (cost=0.00..0.02 rows=1 width=48) (actual time=0.004..0.561 rows=2,564 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.013 45,962.185 ↓ 5.0 5 1

GroupAggregate (cost=12.64..12.66 rows=1 width=80) (actual time=45,962.181..45,962.185 rows=5 loops=1)

  • Group Key: dp_3.woid, dp_3.paymentdate
30. 0.034 45,962.172 ↓ 5.0 5 1

Sort (cost=12.64..12.64 rows=1 width=48) (actual time=45,962.171..45,962.172 rows=5 loops=1)

  • Sort Key: dp_3.woid, dp_3.paymentdate
  • Sort Method: quicksort Memory: 25kB
31. 4,621.737 45,962.138 ↓ 5.0 5 1

Nested Loop (cost=0.00..12.63 rows=1 width=48) (actual time=4,898.188..45,962.138 rows=5 loops=1)

  • Join Filter: ((dp_3.woid)::text = (r_3.woid)::text)
  • Rows Removed by Join Filter: 14,660,947
32. 21.541 26.669 ↓ 2,564.0 2,564 1

CTE Scan on datapool_domain dp_3 (cost=0.00..0.06 rows=1 width=48) (actual time=0.010..26.669 rows=2,564 loops=1)

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

SubPlan (for CTE Scan)

34. 5.128 5.128 ↑ 1.0 1 2,564

CTE Scan on reminder reminder_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=2,564)

35. 0.000 0.000 ↓ 0.0 0 2,564

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

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. 41,313.732 41,313.732 ↓ 1,143.6 5,718 2,564

Function Scan on dblink r_3 (cost=0.00..12.50 rows=5 width=516) (actual time=0.321..16.113 rows=5,718 loops=2,564)

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

CTE reguler

39. 1.507 867.625 ↓ 1,968.0 1,968 1

GroupAggregate (cost=13.95..14.04 rows=1 width=80) (actual time=865.916..867.625 rows=1,968 loops=1)

  • Group Key: dp_4.woid, dp_4.paymentdate
40. 10.066 866.118 ↓ 512.8 2,564 1

Sort (cost=13.95..13.96 rows=5 width=48) (actual time=865.904..866.118 rows=2,564 loops=1)

  • Sort Key: dp_4.woid, dp_4.paymentdate
  • Sort Method: quicksort Memory: 297kB
41. 31.290 856.052 ↓ 512.8 2,564 1

Hash Join (cost=0.09..13.89 rows=5 width=48) (actual time=818.154..856.052 rows=2,564 loops=1)

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

Function Scan on dblink r_4 (cost=0.00..10.00 rows=1,000 width=516) (actual time=815.191..821.876 rows=65,737 loops=1)

43. 0.592 2.886 ↓ 2,564.0 2,564 1

Hash (cost=0.08..0.08 rows=1 width=48) (actual time=2.886..2.886 rows=2,564 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 203kB
44. 2.294 2.294 ↓ 2,564.0 2,564 1

CTE Scan on datapool_domain dp_4 (cost=0.00..0.08 rows=1 width=48) (actual time=0.014..2.294 rows=2,564 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 2,564

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

47. 0.000 0.000 ↓ 0.0 0 2,564

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

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.443 80,115.298 ↓ 703.0 3,515 1

Append (cost=0.00..0.25 rows=5 width=80) (actual time=32,419.322..80,115.298 rows=3,515 loops=1)

51. 32,419.341 32,419.341 ↓ 11.0 11 1

CTE Scan on reminder (cost=0.00..0.04 rows=1 width=80) (actual time=32,419.322..32,419.341 rows=11 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.536 0.536 ↓ 0.0 0 1

CTE Scan on visit (cost=0.00..0.04 rows=1 width=80) (actual time=0.536..0.536 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. 863.325 863.325 ↓ 1,531.0 1,531 1

CTE Scan on eventlist (cost=0.00..0.04 rows=1 width=80) (actual time=860.502..863.325 rows=1,531 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. 45,962.199 45,962.199 ↓ 5.0 5 1

CTE Scan on express (cost=0.00..0.04 rows=1 width=80) (actual time=45,962.190..45,962.199 rows=5 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. 869.454 869.454 ↓ 1,968.0 1,968 1

CTE Scan on reguler (cost=0.00..0.04 rows=1 width=80) (actual time=865.924..869.454 rows=1,968 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.695 ms
Execution time : 80,169.016 ms