explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GB7U

Settings
# exclusive inclusive rows x rows loops node
1. 2.530 40,348.961 ↓ 393.6 1,968 1

GroupAggregate (cost=83.99..84.22 rows=5 width=76) (actual time=40,346.007..40,348.961 rows=1,968 loops=1)

  • Group Key: resultset02.paymentdate, resultset02.woid
2. 14.023 40,346.431 ↓ 1,095.6 5,478 1

Sort (cost=83.99..84.01 rows=5 width=48) (actual time=40,345.993..40,346.431 rows=5,478 loops=1)

  • Sort Key: resultset02.paymentdate, resultset02.woid
  • Sort Method: quicksort Memory: 620kB
3. 1.058 40,332.408 ↓ 1,095.6 5,478 1

Subquery Scan on resultset02 (cost=83.84..83.94 rows=5 width=48) (actual time=40,329.664..40,332.408 rows=5,478 loops=1)

4. 4.976 40,331.350 ↓ 1,095.6 5,478 1

HashAggregate (cost=83.84..83.89 rows=5 width=80) (actual time=40,329.663..40,331.350 rows=5,478 loops=1)

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

CTE datapool_domain

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

Function Scan on dblink r (cost=0.00..25.00 rows=1 width=144) (actual time=1,836.590..1,875.165 rows=2,564 loops=1)

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

CTE reminder

8. 0.020 37,678.778 ↓ 11.0 11 1

GroupAggregate (cost=12.59..12.62 rows=1 width=80) (actual time=37,678.769..37,678.778 rows=11 loops=1)

  • Group Key: dp.woid, dp.paymentdate
9. 0.071 37,678.758 ↓ 12.0 12 1

Sort (cost=12.59..12.60 rows=1 width=48) (actual time=37,678.756..37,678.758 rows=12 loops=1)

  • Sort Key: dp.woid, dp.paymentdate
  • Sort Method: quicksort Memory: 25kB
10. 8,779.587 37,678.687 ↓ 12.0 12 1

Nested Loop (cost=0.00..12.58 rows=1 width=48) (actual time=4,826.659..37,678.687 rows=12 loops=1)

  • Join Filter: ((dp.woid)::text = (r_1.woid)::text)
  • Rows Removed by Join Filter: 15325016
11. 1,879.668 1,879.668 ↓ 2,564.0 2,564 1

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

12. 27,019.432 27,019.432 ↓ 1,195.4 5,977 2,564

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

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

CTE visit

14. 0.002 0.679 ↓ 0.0 0 1

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

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

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

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

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

  • Group Key: dp_2.woid, dp_2.paymentdate
20. 7.977 870.369 ↓ 2,114.0 2,114 1

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

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

Hash Join (cost=0.04..17.64 rows=1 width=48) (actual time=826.974..862.392 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. 851.801 851.801 ↓ 2,264.6 33,969 1

Function Scan on dblink r_2 (cost=0.00..17.50 rows=15 width=1,032) (actual time=825.751..851.801 rows=33,969 loops=1)

  • Filter: (((typejasa)::text = 'PACKAGE'::text) OR ((typejasa)::text = 'KM,PACKAGE'::text) OR ((typejasa)::text = 'PACKAGE,KM'::text))
  • Rows Removed by Filter: 31577
23. 0.576 1.140 ↓ 2,564.0 2,564 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 203kB
24. 0.564 0.564 ↓ 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.003..0.564 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. 1.448 879.372 ↓ 1,968.0 1,968 1

GroupAggregate (cost=14.13..14.20 rows=1 width=80) (actual time=877.720..879.372 rows=1,968 loops=1)

  • Group Key: dp_3.woid, dp_3.paymentdate
30. 10.084 877.924 ↓ 641.0 2,564 1

Sort (cost=14.13..14.14 rows=4 width=48) (actual time=877.710..877.924 rows=2,564 loops=1)

  • Sort Key: dp_3.woid, dp_3.paymentdate
  • Sort Method: quicksort Memory: 297kB
31. 33.520 867.840 ↓ 641.0 2,564 1

Hash Join (cost=0.08..14.10 rows=4 width=48) (actual time=827.639..867.840 rows=2,564 loops=1)

  • Hash Cond: ((r_3.woid)::text = (dp_3.woid)::text)
  • Join Filter: ((((r_3.pittype)::text = 'EXPRESS'::text) AND (NOT (SubPlan 8))) OR (NOT (SubPlan 9)) OR (NOT (SubPlan 10)))
32. 831.363 831.363 ↓ 65.5 65,547 1

Function Scan on dblink r_3 (cost=0.00..10.00 rows=1,000 width=1,032) (actual time=824.597..831.363 rows=65,547 loops=1)

33. 0.596 2.957 ↓ 2,564.0 2,564 1

Hash (cost=0.06..0.06 rows=1 width=48) (actual time=2.956..2.957 rows=2,564 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 203kB
34. 2.361 2.361 ↓ 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.013..2.361 rows=2,564 loops=1)

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

SubPlan (for CTE Scan)

36. 0.000 0.000 ↑ 1.0 1 2,569

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

37. 0.000 0.000 ↓ 0.0 0 5,128

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

38. 0.000 0.000 ↓ 0.0 0

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

39.          

CTE reguler

40. 1.446 892.803 ↓ 1,968.0 1,968 1

GroupAggregate (cost=13.95..14.03 rows=1 width=80) (actual time=891.157..892.803 rows=1,968 loops=1)

  • Group Key: dp_4.woid, dp_4.paymentdate
41. 10.071 891.357 ↓ 512.8 2,564 1

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

  • Sort Key: dp_4.woid, dp_4.paymentdate
  • Sort Method: quicksort Memory: 297kB
42. 32.555 881.286 ↓ 512.8 2,564 1

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

  • Hash Cond: ((r_4.woid)::text = (dp_4.woid)::text)
43. 845.534 845.534 ↓ 65.5 65,547 1

Function Scan on dblink r_4 (cost=0.00..10.00 rows=1,000 width=516) (actual time=838.362..845.534 rows=65,547 loops=1)

44. 0.627 3.197 ↓ 2,564.0 2,564 1

Hash (cost=0.07..0.07 rows=1 width=48) (actual time=3.196..3.197 rows=2,564 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 203kB
45. 2.570 2.570 ↓ 2,564.0 2,564 1

CTE Scan on datapool_domain dp_4 (cost=0.00..0.07 rows=1 width=48) (actual time=0.014..2.570 rows=2,564 loops=1)

  • Filter: ((NOT (SubPlan 12)) OR (NOT (SubPlan 13)) OR (NOT (SubPlan 14)) OR (NOT (SubPlan 15)))
46.          

SubPlan (for CTE Scan)

47. 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)

48. 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)

49. 0.000 0.000 ↓ 0.0 0

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

50. 0.000 0.000 ↓ 0.0 0

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

51. 0.696 40,326.374 ↓ 1,095.6 5,478 1

Append (cost=0.00..0.17 rows=5 width=80) (actual time=37,678.773..40,326.374 rows=5,478 loops=1)

52. 37,678.795 37,678.795 ↓ 11.0 11 1

CTE Scan on reminder (cost=0.00..0.02 rows=1 width=80) (actual time=37,678.772..37,678.795 rows=11 loops=1)

53. 0.680 0.680 ↓ 0.0 0 1

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

54. 872.244 872.244 ↓ 1,531.0 1,531 1

CTE Scan on eventlist (cost=0.00..0.02 rows=1 width=80) (actual time=870.218..872.244 rows=1,531 loops=1)

55. 880.265 880.265 ↓ 1,968.0 1,968 1

CTE Scan on express (cost=0.00..0.02 rows=1 width=80) (actual time=877.723..880.265 rows=1,968 loops=1)

56. 893.694 893.694 ↓ 1,968.0 1,968 1

CTE Scan on reguler (cost=0.00..0.02 rows=1 width=80) (actual time=891.160..893.694 rows=1,968 loops=1)

Planning time : 1.897 ms
Execution time : 40,387.912 ms