74 подписчика

--План выполнения тестового запроса -ARRAY

Sort (cost=602598.54..603774.42 rows=470349 width=80) (actual time=32916.398..33173.216 rows=1078047 loops=1)

Sort Key: b.book_date, tn.ticket_no

Sort Method: external merge Disk: 78992kB

CTE random_period

-> Limit (cost=191879.84..191879.84 rows=1 width=24) (actual time=4366.534..4366.546 rows=1 loops=1)

-> Sort (cost=191879.84..209662.82 rows=7113192 width=24) (actual time=4366.531..4366.534 rows=1 loops=1)

Sort Key: (random())

Sort Method: top-N heapsort Memory: 25kB

-> Seq Scan on bookings (cost=0.00..156313.88 rows=7113192 width=24) (actual time=0.147..3051.234 rows=7113192 loops=1)

-> Hash Left Join (cost=86766.61..332003.89 rows=470349 width=80) (actual time=12377.785..31429.152 rows=1078047 loops=1)

Hash Cond: (s.flight_id = f.flight_id)

-> Nested Loop Left Join (cost=83163.83..327166.39 rows=470349 width=72) (actual time=12311.365..30556.856 rows=1078047 loops=1)

-> Nested Loop Left Join (cost=83163.26..104902.04 rows=355660 width=82) (actual time=12311.267..23764.409 rows=821542 loops=1)

-> Nested Loop (cost=83162.69..95979.99 rows=355660 width=55) (actual time=12311.155..14786.294 rows=821542 loops=1)

-> Finalize GroupAggregate (cost=83162.69..88866.79 rows=35566 width=55) (actual time=12311.108..13733.846 rows=388524 loops=1)

Group Key: b.book_ref

InitPlan 2

-> CTE Scan on random_period (cost=0.00..0.02 rows=1 width=8) (actual time=4366.541..4366.544 rows=1 loops=1)

InitPlan 3

-> CTE Scan on random_period random_period_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

-> Gather Merge (cost=83162.65..88066.51 rows=35566 width=55) (actual time=12311.079..13099.448 rows=388524 loops=1)

Workers Planned: 1

Workers Launched: 1

-> Partial GroupAggregate (cost=82162.64..83065.33 rows=35566 width=55) (actual time=7912.508..8455.765 rows=194262 loops=2)

Group Key: b.book_ref

-> Sort (cost=82162.64..82277.17 rows=45811 width=29) (actual time=7912.458..8132.223 rows=410771 loops=2)

Sort Key: b.book_ref

Sort Method: external merge Disk: 16168kB

Worker 0: Sort Method: external merge Disk: 16016kB

-> Nested Loop Left Join (cost=0.87..78616.09 rows=45811 width=29) (actual time=1.708..7358.141 rows=410771 loops=2)

-> Parallel Index Scan using idx_bookings_book_date on bookings b (cost=0.43..982.51 rows=20921 width=15) (actual time=0.892..210.773 rows=194262 loops=2)

Index Cond: ((book_date >= (InitPlan 2).col1) AND (book_date <= (InitPlan 3).col1))

-> Index Scan using idx_tickets_book_ref on tickets t_1 (cost=0.43..3.68 rows=3 width=21) (actual time=0.035..0.036 rows=2 loops=388524)

Index Cond: (book_ref = b.book_ref)

-> Function Scan on unnest tn (cost=0.00..0.10 rows=10 width=32) (actual time=0.001..0.001 rows=2 loops=388524)

-> Memoize (cost=0.57..2.79 rows=1 width=27) (actual time=0.010..0.010 rows=1 loops=821542)

Cache Key: tn.ticket_no

Cache Mode: logical

Hits: 0 Misses: 821542 Evictions: 607974 Overflows: 0 Memory Usage: 28673kB

-> Index Scan using tickets_pkey on tickets t (cost=0.56..2.78 rows=1 width=27) (actual time=0.009..0.009 rows=1 loops=821542)

Index Cond: (ticket_no = tn.ticket_no)

-> Index Only Scan using segments_pkey on segments s (cost=0.56..0.60 rows=2 width=18) (actual time=0.007..0.008 rows=1 loops=821542)

Index Cond: (ticket_no = t.ticket_no)

Heap Fetches: 0

-> Hash (cost=2247.35..2247.35 rows=108435 width=12) (actual time=66.115..66.117 rows=108435 loops=1)

Buckets: 131072 Batche

2 минуты

30 ноября 2025