explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SiQ7

Settings
# exclusive inclusive rows x rows loops node
1. 3,199.831 286,090.132 ↓ 1.1 10,734,256 1

Unique (cost=14,611,915.49..14,674,252.55 rows=9,848,779 width=358) (actual time=275,286.599..286,090.132 rows=10,734,256 loops=1)

2.          

CTE avg

3. 0.383 1,436.419 ↑ 1.1 201 1

Finalize GroupAggregate (cost=476,322.81..476,329.76 rows=214 width=46) (actual time=1,436.023..1,436.419 rows=201 loops=1)

  • Group Key: x_seats_1.price_level
4. 0.965 1,436.036 ↓ 1.4 589 1

Sort (cost=476,322.81..476,323.88 rows=428 width=46) (actual time=1,436.006..1,436.036 rows=589 loops=1)

  • Sort Key: x_seats_1.price_level
  • Sort Method: quicksort Memory: 107kB
5. 5.567 1,435.071 ↓ 1.4 589 1

Gather (cost=476,258.63..476,304.10 rows=428 width=46) (actual time=1,433.492..1,435.071 rows=589 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 97.646 1,429.504 ↑ 1.1 196 3

Partial HashAggregate (cost=475,258.63..475,261.30 rows=214 width=46) (actual time=1,429.365..1,429.504 rows=196 loops=3)

  • Group Key: x_seats_1.price_level
7. 1,331.858 1,331.858 ↓ 1.7 306,255 3

Parallel Seq Scan on x_seats x_seats_1 (cost=0.00..474,368.33 rows=178,060 width=17) (actual time=0.310..1,331.858 rows=306,255 loops=3)

  • Filter: (((status)::text ~~ 'Sold%'::text) AND ((price_code)::text = 'Adult'::text))
  • Rows Removed by Filter: 3847640
8.          

CTE scan_dates

9. 1,123.695 4,434.671 ↓ 1.5 7,035,003 1

Unique (cost=0.43..770,369.61 rows=4,787,645 width=16) (actual time=0.013..4,434.671 rows=7,035,003 loops=1)

10. 3,310.976 3,310.976 ↑ 1.0 7,390,825 1

Index Only Scan using rockets_scans_ticket_id_scan_date on scans (cost=0.43..751,745.90 rows=7,449,481 width=16) (actual time=0.012..3,310.976 rows=7,390,825 loops=1)

  • Heap Fetches: 7390825
11.          

CTE x_scans

12. 4,418.861 15,938.773 ↓ 1.5 7,035,003 1

Hash Join (cost=644,937.43..865,122.91 rows=4,787,645 width=34) (actual time=5,420.238..15,938.773 rows=7,035,003 loops=1)

  • Hash Cond: (scan_dates.ticket_id = tickets.ticket_id)
13. 6,122.337 6,122.337 ↓ 1.5 7,035,003 1

CTE Scan on scan_dates (cost=0.00..95,752.90 rows=4,787,645 width=16) (actual time=0.013..6,122.337 rows=7,035,003 loops=1)

14. 2,211.607 5,397.575 ↑ 1.0 9,516,496 1

Hash (cost=460,773.19..460,773.19 rows=9,524,419 width=26) (actual time=5,397.575..5,397.575 rows=9,516,496 loops=1)

  • Buckets: 65536 Batches: 256 Memory Usage: 2822kB
15. 3,185.968 3,185.968 ↑ 1.0 9,516,496 1

Seq Scan on tickets (cost=0.00..460,773.19 rows=9,524,419 width=26) (actual time=0.112..3,185.968 rows=9,516,496 loops=1)

16. 162,282.781 282,890.301 ↑ 1.0 12,461,719 1

Sort (cost=12,500,093.21..12,531,261.74 rows=12,467,412 width=358) (actual time=275,286.597..282,890.301 rows=12,461,719 loops=1)

  • Sort Key: x_seats.master_ticket_id
  • Sort Method: external merge Disk: 3121296kB
17. 47,539.886 120,607.520 ↑ 1.0 12,461,719 1

Hash Left Join (cost=1,183,351.81..4,894,400.31 rows=12,467,412 width=358) (actual time=45,699.349..120,607.520 rows=12,461,719 loops=1)

  • Hash Cond: ((x_seats.price_level)::text = (avg.price_level)::text)
18. 10,558.280 71,631.113 ↑ 1.0 12,461,719 1

Merge Left Join (cost=1,183,344.85..3,476,225.24 rows=12,467,412 width=254) (actual time=44,262.782..71,631.113 rows=12,461,719 loops=1)

  • Merge Cond: ((x_seats.event_id = ((x_scans.vt_event_id)::numeric)) AND ((x_seats.seat)::text = (x_scans.seat_name)::text) AND (x_seats."row" = (x_scans.row_name)::text) AND ((x_seats.section)::text = (x_scans.section_name)::text))
19. 7,926.515 7,926.515 ↑ 1.0 12,461,684 1

Index Scan using x_seats_event_seat_composite_idx on x_seats (cost=0.56..2,053,198.33 rows=12,467,412 width=246) (actual time=0.014..7,926.515 rows=12,461,684 loops=1)

20. 736.392 53,146.318 ↓ 1.5 7,065,641 1

Materialize (cost=1,183,344.29..1,207,282.52 rows=4,787,645 width=112) (actual time=44,262.743..53,146.318 rows=7,065,641 loops=1)

21. 33,296.202 52,409.926 ↓ 1.5 7,033,919 1

Sort (cost=1,183,344.29..1,195,313.40 rows=4,787,645 width=112) (actual time=44,262.739..52,409.926 rows=7,033,919 loops=1)

  • Sort Key: ((x_scans.vt_event_id)::numeric), x_scans.seat_name, x_scans.row_name, x_scans.section_name
  • Sort Method: external merge Disk: 286424kB
22. 19,113.724 19,113.724 ↓ 1.5 7,035,003 1

CTE Scan on x_scans (cost=0.00..95,752.90 rows=4,787,645 width=112) (actual time=5,420.244..19,113.724 rows=7,035,003 loops=1)

23. 0.049 1,436.521 ↑ 1.1 201 1

Hash (cost=4.28..4.28 rows=214 width=64) (actual time=1,436.521..1,436.521 rows=201 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
24. 1,436.472 1,436.472 ↑ 1.1 201 1

CTE Scan on avg (cost=0.00..4.28 rows=214 width=64) (actual time=1,436.026..1,436.472 rows=201 loops=1)

Planning time : 0.560 ms
Execution time : 286,960.520 ms