explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8o4a

Settings
# exclusive inclusive rows x rows loops node
1. 3.368 31,188.131 ↓ 1.4 1,255 1

Sort (cost=1,955,338.82..1,955,341.14 rows=925 width=49) (actual time=31,188.088..31,188.131 rows=1,255 loops=1)

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

CTE tickets

3. 0.000 35.561 ↓ 1.9 120,153 1

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

  • Workers Planned: 2
  • Workers Launched: 2
4. 7.079 39.131 ↓ 1.5 40,051 3 / 3

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

  • Hash Cond: (t_2."TicketTypeID" = tt."ID")
5. 28.665 31.955 ↑ 1.3 40,424 3 / 3

Parallel Bitmap Heap Scan on "Tickets" t_2 (cost=2,743.63..154,763.34 rows=53,008 width=50) (actual time=3.744..31.955 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=884
6. 3.290 3.290 ↓ 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=9.870..9.870 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.097 ↓ 1.5 3 3 / 3

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

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

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

  • Filter: "PositiveResponseRequired
  • Rows Removed by Filter: 1
9. 0.332 31,184.763 ↓ 1.4 1,255 1

Hash Join (cost=1,792,861.66..1,792,881.28 rows=925 width=49) (actual time=31,183.989..31,184.763 rows=1,255 loops=1)

  • Hash Cond: (d."ServiceAreaID" = sa."ID")
10. 0.335 31,183.458 ↓ 1.4 1,255 1

Merge Left Join (cost=1,792,757.58..1,792,772.45 rows=925 width=32) (actual time=31,182.998..31,183.458 rows=1,255 loops=1)

  • Merge Cond: (d."ServiceAreaID" = ot."ServiceAreaID")
11. 0.346 11,691.726 ↓ 1.4 1,255 1

Sort (cost=781,002.25..781,004.57 rows=925 width=24) (actual time=11,691.652..11,691.726 rows=1,255 loops=1)

  • Sort Key: d."ServiceAreaID
  • Sort Method: quicksort Memory: 147kB
12. 0.065 11,691.380 ↓ 1.4 1,255 1

Subquery Scan on d (cost=780,938.18..780,956.68 rows=925 width=24) (actual time=11,691.128..11,691.380 rows=1,255 loops=1)

13. 114.691 11,691.315 ↓ 1.4 1,255 1

HashAggregate (cost=780,938.18..780,947.43 rows=925 width=24) (actual time=11,691.127..11,691.315 rows=1,255 loops=1)

  • Group Key: tr."ServiceAreaID
14. 5,147.467 11,576.624 ↑ 3.2 786,637 1

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

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

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

16. 2,426.639 6,364.075 ↑ 1.0 11,812,573 1

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

  • Buckets: 65536 Batches: 256 Memory Usage: 3199kB
17. 3,937.436 3,937.436 ↑ 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=0.075..3,937.436 rows=11,812,573 loops=1)

  • Filter: ("ResponseRequired" AND "Current")
  • Rows Removed by Filter: 3127354
18. 0.452 19,491.397 ↓ 5.7 1,134 1

Sort (cost=1,011,755.32..1,011,755.82 rows=200 width=24) (actual time=19,491.342..19,491.397 rows=1,134 loops=1)

  • Sort Key: ot."ServiceAreaID
  • Sort Method: quicksort Memory: 137kB
19. 0.115 19,490.945 ↓ 5.7 1,134 1

Subquery Scan on ot (cost=1,011,743.68..1,011,747.68 rows=200 width=24) (actual time=19,490.596..19,490.945 rows=1,134 loops=1)

20. 93.278 19,490.830 ↓ 5.7 1,134 1

HashAggregate (cost=1,011,743.68..1,011,745.68 rows=200 width=24) (actual time=19,490.595..19,490.830 rows=1,134 loops=1)

  • Group Key: tr_1."ServiceAreaID
21. 78.401 19,397.552 ↑ 1.6 612,005 1

Unique (cost=989,237.87..996,739.81 rows=1,000,258 width=26) (actual time=18,879.135..19,397.552 rows=612,005 loops=1)

22. 2,713.960 19,319.151 ↑ 1.5 670,158 1

Sort (cost=989,237.87..991,738.52 rows=1,000,258 width=26) (actual time=18,879.133..19,319.151 rows=670,158 loops=1)

  • Sort Key: tr_1."TicketNumber", tr_1."ServiceAreaID
  • Sort Method: external merge Disk: 23672kB
23. 8,858.763 16,605.191 ↑ 1.5 670,158 1

Hash Join (cost=711,198.95..865,619.46 rows=1,000,258 width=26) (actual time=7,770.511..16,605.191 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
24. 18.958 18.958 ↓ 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.060..18.958 rows=120,153 loops=1)

25. 3,579.965 7,727.470 ↓ 1.0 14,323,158 1

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

  • Buckets: 65536 Batches: 512 Memory Usage: 2544kB
26. 4,147.505 4,147.505 ↓ 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=36.852..4,147.505 rows=14,323,158 loops=1)

  • Filter: (("ResponseID" IS NOT NULL) AND "ResponseRequired")
  • Rows Removed by Filter: 616769
27. 0.362 0.973 ↑ 1.0 2,226 1

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

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

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

Planning time : 2.287 ms
Execution time : 31,203.423 ms