explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lmwu : Optimization for: plan #FDLi

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 1,192.049 ↓ 1.2 1,255 1

Finalize GroupAggregate (cost=435,800.42..436,059.09 rows=1,021 width=24) (actual time=1,190.658..1,192.049 rows=1,255 loops=1)

  • Group Key: tsa."ServiceAreaID
2. 54.599 1,203.692 ↓ 1.8 3,578 1

Gather Merge (cost=435,800.42..436,038.67 rows=2,042 width=24) (actual time=1,190.653..1,203.692 rows=3,578 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 0.465 1,149.093 ↓ 1.2 1,193 3 / 3

Sort (cost=434,800.39..434,802.94 rows=1,021 width=24) (actual time=1,149.034..1,149.093 rows=1,193 loops=3)

  • Sort Key: tsa."ServiceAreaID
  • Sort Method: quicksort Memory: 142kB
  • Worker 0: Sort Method: quicksort Memory: 142kB
  • Worker 1: Sort Method: quicksort Memory: 141kB
4. 67.542 1,148.628 ↓ 1.2 1,193 3 / 3

Partial HashAggregate (cost=434,739.15..434,749.36 rows=1,021 width=24) (actual time=1,148.468..1,148.628 rows=1,193 loops=3)

  • Group Key: tsa."ServiceAreaID
5. 38.942 1,081.086 ↓ 1.5 262,211 3 / 3

Nested Loop (cost=2,745.26..433,841.17 rows=179,597 width=16) (actual time=4.957..1,081.086 rows=262,211 loops=3)

6. 15.047 80.920 ↓ 1.5 40,051 3 / 3

Hash Join (cost=2,744.70..155,050.98 rows=26,504 width=16) (actual time=4.780..80.920 rows=40,051 loops=3)

  • Hash Cond: (t."TicketTypeID" = tt."ID")
7. 61.711 65.766 ↑ 1.3 40,424 3 / 3

Parallel Bitmap Heap Scan on "Tickets" t (cost=2,743.63..154,763.34 rows=53,008 width=32) (actual time=4.623..65.766 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=3135
8. 4.055 4.055 ↓ 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=12.166..12.166 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))
9. 0.009 0.107 ↓ 1.5 3 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.098 0.098 ↓ 1.5 3 3 / 3

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

  • Filter: "PositiveResponseRequired
  • Rows Removed by Filter: 1
11. 961.224 961.224 ↑ 7.1 7 120,153 / 3

Index Scan using "IX_TicketServiceAreas_TicketID" on "TicketServiceAreas" tsa (cost=0.56..10.02 rows=50 width=32) (actual time=0.022..0.024 rows=7 loops=120,153)

  • Index Cond: ("TicketID" = t."ID")
Planning time : 1.390 ms
Execution time : 1,204.539 ms