explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SMj5

Settings
# exclusive inclusive rows x rows loops node
1. 2.660 30,894.241 ↓ 1.4 1,255 1

Sort (cost=1,883,083.76..1,883,086.07 rows=925 width=49) (actual time=30,894.205..30,894.241 rows=1,255 loops=1)

  • Sort Key: sa."Code", sa."Name
  • Sort Method: quicksort Memory: 190kB
2.          

CTE tickets

3. 0.000 42.790 ↓ 1.9 120,153 1

Gather (cost=3,744.70..162,411.98 rows=63,610 width=34) (actual time=14.088..42.790 rows=120,153 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 7.694 48.801 ↓ 1.5 40,051 3 / 3

Hash Join (cost=2,744.70..155,050.98 rows=26,504 width=34) (actual time=4.466..48.801 rows=40,051 loops=3)

  • Hash Cond: (t_3."TicketTypeID" = tt."ID")
5. 37.366 41.016 ↑ 1.3 40,424 3 / 3

Parallel Bitmap Heap Scan on "Tickets" t_3 (cost=2,743.63..154,763.34 rows=53,008 width=50) (actual time=4.276..41.016 rows=40,424 loops=3)

  • Recheck Cond: (("ResponseDueDate" >= '2020-01-01 00:00:00-05'::timestamp with time zone) AND ("ResponseDueDate" < '2020-02-01 00:00:00-05'::timestamp with time zone))
  • Filter: ((NOT "IsCanceled") AND (("ChildTicketNumber" IS NULL) OR (("TicketNumber")::text <> ("ChildTicketNumber")::text)) AND ("Status" = 2))
  • Rows Removed by Filter: 4488
  • Heap Blocks: exact=1080
6. 3.650 3.650 ↓ 1.0 134,734 1 / 3

Bitmap Index Scan on "IX_Tickets_ResponseDueDate" (cost=0.00..2,711.83 rows=129,140 width=0) (actual time=10.951..10.951 rows=134,734 loops=1)

  • Index Cond: (("ResponseDueDate" >= '2020-01-01 00:00:00-05'::timestamp with time zone) AND ("ResponseDueDate" < '2020-02-01 00:00:00-05'::timestamp with time zone))
7. 0.014 0.091 ↓ 1.5 3 3 / 3

Hash (cost=1.04..1.04 rows=2 width=16) (actual time=0.091..0.091 rows=3 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.077 0.077 ↓ 1.5 3 3 / 3

Seq Scan on "TicketTypes" tt (cost=0.00..1.04 rows=2 width=16) (actual time=0.076..0.077 rows=3 loops=3)

  • Filter: "PositiveResponseRequired
  • Rows Removed by Filter: 1
9. 0.342 30,891.581 ↓ 1.4 1,255 1

Hash Join (cost=1,720,606.60..1,720,626.21 rows=925 width=49) (actual time=30,890.911..30,891.581 rows=1,255 loops=1)

  • Hash Cond: (d."ServiceAreaID" = sa."ID")
10. 0.279 30,890.366 ↓ 1.4 1,255 1

Merge Left Join (cost=1,720,502.51..1,720,517.39 rows=925 width=32) (actual time=30,889.976..30,890.366 rows=1,255 loops=1)

  • Merge Cond: (d."ServiceAreaID" = ot."ServiceAreaID")
11. 0.347 10,812.802 ↓ 1.4 1,255 1

Sort (cost=781,002.25..781,004.57 rows=925 width=24) (actual time=10,812.732..10,812.802 rows=1,255 loops=1)

  • Sort Key: d."ServiceAreaID
  • Sort Method: quicksort Memory: 147kB
12. 0.095 10,812.455 ↓ 1.4 1,255 1

Subquery Scan on d (cost=780,938.18..780,956.68 rows=925 width=24) (actual time=10,812.221..10,812.455 rows=1,255 loops=1)

13. 107.374 10,812.360 ↓ 1.4 1,255 1

HashAggregate (cost=780,938.18..780,947.43 rows=925 width=24) (actual time=10,812.220..10,812.360 rows=1,255 loops=1)

  • Group Key: tr."ServiceAreaID
14. 4,516.653 10,704.986 ↑ 3.2 786,637 1

Hash Join (cost=654,944.16..768,276.46 rows=2,532,345 width=16) (actual time=6,150.550..10,704.986 rows=786,637 loops=1)

  • Hash Cond: ((t."TicketNumber")::text = (tr."TicketNumber")::text)
15. 79.315 79.315 ↓ 1.9 120,153 1

CTE Scan on tickets t (cost=0.00..1,272.20 rows=63,610 width=58) (actual time=14.096..79.315 rows=120,153 loops=1)

16. 2,322.260 6,109.018 ↑ 1.0 11,812,573 1

Hash (cost=425,169.27..425,169.27 rows=11,883,271 width=26) (actual time=6,109.018..6,109.018 rows=11,812,573 loops=1)

  • Buckets: 65536 Batches: 256 Memory Usage: 3199kB
17. 3,786.758 3,786.758 ↑ 1.0 11,812,573 1

Seq Scan on "TicketResponses" tr (cost=0.00..425,169.27 rows=11,883,271 width=26) (actual time=1.141..3,786.758 rows=11,812,573 loops=1)

  • Filter: ("ResponseRequired" AND "Current")
  • Rows Removed by Filter: 3127354
18. 0.282 20,077.285 ↓ 5.7 1,134 1

Sort (cost=939,500.26..939,500.76 rows=200 width=24) (actual time=20,077.240..20,077.285 rows=1,134 loops=1)

  • Sort Key: ot."ServiceAreaID
  • Sort Method: quicksort Memory: 137kB
19. 0.058 20,077.003 ↓ 5.7 1,134 1

Subquery Scan on ot (cost=939,488.61..939,492.61 rows=200 width=24) (actual time=20,076.841..20,077.003 rows=1,134 loops=1)

20. 90.615 20,076.945 ↓ 5.7 1,134 1

HashAggregate (cost=939,488.61..939,490.61 rows=200 width=24) (actual time=20,076.840..20,076.945 rows=1,134 loops=1)

  • Group Key: tr_1."ServiceAreaID
21. 79.405 19,986.330 ↓ 2.9 612,005 1

Unique (cost=934,692.58..936,291.26 rows=213,157 width=26) (actual time=19,477.240..19,986.330 rows=612,005 loops=1)

22. 2,617.954 19,906.925 ↓ 3.1 670,158 1

Sort (cost=934,692.58..935,225.47 rows=213,157 width=26) (actual time=19,477.238..19,906.925 rows=670,158 loops=1)

  • Sort Key: tr_1."TicketNumber", tr_1."ServiceAreaID
  • Sort Method: external merge Disk: 23672kB
23. 715.389 17,288.971 ↓ 3.1 670,158 1

Hash Join (cost=891,798.68..910,723.53 rows=213,157 width=26) (actual time=16,563.592..17,288.971 rows=670,158 loops=1)

  • Hash Cond: ((t_2."TicketNumber")::text = (tr_1."TicketNumber")::text)
24. 16.939 16.939 ↓ 1.9 120,153 1

CTE Scan on tickets t_2 (cost=0.00..1,272.20 rows=63,610 width=58) (actual time=0.029..16.939 rows=120,153 loops=1)

25. 150.968 16,556.643 ↑ 1.5 670,158 1

Hash (cost=865,619.46..865,619.46 rows=1,000,258 width=84) (actual time=16,556.643..16,556.643 rows=670,158 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 1665kB
26. 8,177.256 16,405.675 ↑ 1.5 670,158 1

Hash Join (cost=711,198.95..865,619.46 rows=1,000,258 width=84) (actual time=8,256.684..16,405.675 rows=670,158 loops=1)

  • Hash Cond: ((t_1."TicketNumber")::text = (tr_1."TicketNumber")::text)
  • Join Filter: (tr_1."EnteredDate" <= t_1."ResponseDueDate")
  • Rows Removed by Join Filter: 145073
27. 19.473 19.473 ↓ 1.9 120,153 1

CTE Scan on tickets t_1 (cost=0.00..1,272.20 rows=63,610 width=66) (actual time=0.001..19.473 rows=120,153 loops=1)

28. 4,173.442 8,208.946 ↓ 1.0 14,323,158 1

Hash (cost=425,169.27..425,169.27 rows=14,081,414 width=34) (actual time=8,208.946..8,208.946 rows=14,323,158 loops=1)

  • Buckets: 65536 Batches: 512 Memory Usage: 2544kB
29. 4,035.504 4,035.504 ↓ 1.0 14,323,158 1

Seq Scan on "TicketResponses" tr_1 (cost=0.00..425,169.27 rows=14,081,414 width=34) (actual time=39.677..4,035.504 rows=14,323,158 loops=1)

  • Filter: (("ResponseID" IS NOT NULL) AND "ResponseRequired")
  • Rows Removed by Filter: 616769
30. 0.379 0.873 ↑ 1.0 2,226 1

Hash (cost=76.26..76.26 rows=2,226 width=49) (actual time=0.873..0.873 rows=2,226 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 212kB
31. 0.494 0.494 ↑ 1.0 2,226 1

Seq Scan on "ServiceAreas" sa (cost=0.00..76.26 rows=2,226 width=49) (actual time=0.055..0.494 rows=2,226 loops=1)

Planning time : 27.659 ms
Execution time : 30,909.997 ms