explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ETWj

Settings
# exclusive inclusive rows x rows loops node
1. 27.210 20,663.336 ↑ 1.0 1 1

Aggregate (cost=1,171,652.62..1,171,652.63 rows=1 width=8) (actual time=20,663.336..20,663.336 rows=1 loops=1)

2.          

CTE tickets

3. 0.000 64.211 ↓ 1.9 120,153 1

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

  • Workers Planned: 2
  • Workers Launched: 2
4. 7.950 70.182 ↓ 1.5 40,051 3 / 3

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

  • Hash Cond: (t_1."TicketTypeID" = tt."ID")
5. 56.080 62.109 ↑ 1.3 40,424 3 / 3

Parallel Bitmap Heap Scan on "Tickets" t_1 (cost=2,743.63..154,763.34 rows=53,008 width=50) (actual time=6.720..62.109 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=920
6. 6.029 6.029 ↓ 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=18.088..18.088 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.015 0.123 ↓ 1.5 3 3 / 3

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

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

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

  • Filter: "PositiveResponseRequired
  • Rows Removed by Filter: 1
9. 91.007 20,636.126 ↑ 1.6 612,005 1

Unique (cost=989,235.51..996,737.43 rows=1,000,257 width=26) (actual time=20,073.434..20,636.126 rows=612,005 loops=1)

10. 2,985.076 20,545.119 ↑ 1.5 670,158 1

Sort (cost=989,235.51..991,736.15 rows=1,000,257 width=26) (actual time=20,073.432..20,545.119 rows=670,158 loops=1)

  • Sort Key: tr."TicketNumber", tr."ServiceAreaID
  • Sort Method: external merge Disk: 23672kB
11. 9,120.358 17,560.043 ↑ 1.5 670,158 1

Hash Join (cost=711,197.71..865,617.20 rows=1,000,257 width=26) (actual time=8,405.070..17,560.043 rows=670,158 loops=1)

  • Hash Cond: ((t."TicketNumber")::text = (tr."TicketNumber")::text)
  • Join Filter: (tr."EnteredDate" <= t."ResponseDueDate")
  • Rows Removed by Join Filter: 145073
12. 103.082 103.082 ↓ 1.9 120,153 1

CTE Scan on tickets t (cost=0.00..1,272.20 rows=63,610 width=66) (actual time=20.977..103.082 rows=120,153 loops=1)

13. 3,833.981 8,336.603 ↓ 1.0 14,323,158 1

Hash (cost=425,169.16..425,169.16 rows=14,081,404 width=34) (actual time=8,336.602..8,336.603 rows=14,323,158 loops=1)

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

Seq Scan on "TicketResponses" tr (cost=0.00..425,169.16 rows=14,081,404 width=34) (actual time=71.969..4,502.622 rows=14,323,158 loops=1)

  • Filter: (("ResponseID" IS NOT NULL) AND "ResponseRequired")
  • Rows Removed by Filter: 616769
Planning time : 2.799 ms
Execution time : 20,680.618 ms