explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iAu

Settings
# exclusive inclusive rows x rows loops node
1. 126.249 18,790.977 ↑ 32.3 738 1

HashAggregate (cost=192,432.89..1,180,191.57 rows=23,833 width=22) (actual time=18,709.915..18,790.977 rows=738 loops=1)

  • Group Key: c.ticketno
  • Filter: (max(c.departuredate) < (now() - '5 days'::interval))
  • Rows Removed by Filter: 76038
2.          

CTE tickets

3. 882.618 882.618 ↑ 1.0 1,514,128 1

Seq Scan on gdsticketcoupon (cost=0.00..86,712.29 rows=1,541,382 width=14) (actual time=0.008..882.618 rows=1,514,128 loops=1)

  • Filter: ((couponstatus)::text = ANY ('{AirportControl,OriginalIssue,Suspended,NotAvailable}'::text[]))
  • Rows Removed by Filter: 957091
4. 63.795 18,611.592 ↓ 3.9 238,704 1

Nested Loop (cost=35,010.09..105,267.09 rows=60,468 width=22) (actual time=1,764.103..18,611.592 rows=238,704 loops=1)

5. 649.635 1,970.887 ↓ 2,368.1 473,626 1

HashAggregate (cost=34,681.10..34,683.10 rows=200 width=52) (actual time=1,759.869..1,970.887 rows=473,626 loops=1)

  • Group Key: (tickets.ticketno)::text
6. 1,321.252 1,321.252 ↑ 1.0 1,514,128 1

CTE Scan on tickets (cost=0.00..30,827.64 rows=1,541,382 width=52) (actual time=0.011..1,321.252 rows=1,514,128 loops=1)

7. 16,574.365 16,576.910 ↑ 1.0 1 473,626

Index Scan using gdsticketcoupon_pkey on gdsticketcoupon c (cost=328.99..352.91 rows=1 width=22) (actual time=0.034..0.035 rows=1 loops=473,626)

  • Index Cond: ((ticketno)::text = (tickets.ticketno)::text)
  • Filter: ((NOT (hashed SubPlan 5)) AND (departuredate >= (now() - '6 days'::interval)))
  • Rows Removed by Filter: 3
8.          

SubPlan (forIndex Scan)

9. 2.545 2.545 ↑ 1.0 8,967 1

Seq Scan on unusedtickets (cost=0.00..305.25 rows=9,325 width=14) (actual time=0.010..2.545 rows=8,967 loops=1)

10.          

SubPlan (forHashAggregate)

11. 25.830 25.830 ↑ 1.0 1 738

Index Scan using gdsticket_pkey on gdsticket t (cost=0.42..8.44 rows=1 width=8) (actual time=0.035..0.035 rows=1 loops=738)

  • Index Cond: ((ticketno)::text = (c.ticketno)::text)
12. 5.166 5.166 ↑ 1.0 1 738

Index Scan using gdsticket_pkey on gdsticket t_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=738)

  • Index Cond: ((ticketno)::text = (c.ticketno)::text)
13. 1.476 22.140 ↑ 1.0 1 738

Aggregate (cost=24.53..24.54 rows=1 width=10) (actual time=0.030..0.030 rows=1 loops=738)

14. 20.664 20.664 ↑ 1.7 3 738

Index Scan using gdsticketcoupon_pkey on gdsticketcoupon gdsticketcoupon_1 (cost=0.43..24.52 rows=5 width=10) (actual time=0.023..0.028 rows=3 loops=738)

  • Index Cond: ((ticketno)::text = (c.ticketno)::text)