explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZRGG

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 10,439.251 ↑ 24.5 5,258 1

Finalize GroupAggregate (cost=1,909,164.17..1,929,707.90 rows=128,617 width=184) (actual time=10,419.417..10,439.251 rows=5,258 loops=1)

  • Group Key: br.id, (btrim(((((COALESCE(g.surname, ''''::text) || ' '::text) || COALESCE(g.name, ''''::text)) || ' '::text) || COALESCE(g.patronymic, ' '::text)))), r.title, cp.title, rp2.sum, ct.title, ct.request_id
2. 319.710 10,733.937 ↑ 20.4 5,258 1

Gather Merge (cost=1,909,164.17..1,924,080.92 rows=107,180 width=184) (actual time=10,419.392..10,733.937 rows=5,258 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 7.649 10,414.227 ↑ 30.6 1,753 3 / 3

Partial GroupAggregate (cost=1,908,164.14..1,910,709.67 rows=53,590 width=184) (actual time=10,405.186..10,414.227 rows=1,753 loops=3)

  • Group Key: br.id, (btrim(((((COALESCE(g.surname, ''''::text) || ' '::text) || COALESCE(g.name, ''''::text)) || ' '::text) || COALESCE(g.patronymic, ' '::text)))), r.title, cp.title, rp2.sum, ct.title, ct.request_id
4. 18.393 10,406.578 ↑ 4.7 11,467 3 / 3

Sort (cost=1,908,164.14..1,908,298.12 rows=53,590 width=184) (actual time=10,405.132..10,406.578 rows=11,467 loops=3)

  • Sort Key: br.id, (btrim(((((COALESCE(g.surname, ''''::text) || ' '::text) || COALESCE(g.name, ''''::text)) || ' '::text) || COALESCE(g.patronymic, ' '::text)))), r.title, cp.title, rp2.sum, ct.title, ct.request_id
  • Sort Method: quicksort Memory: 2,033kB
  • Worker 0: Sort Method: quicksort Memory: 2,092kB
  • Worker 1: Sort Method: quicksort Memory: 1,855kB
5. 1,037.164 10,388.185 ↑ 4.7 11,467 3 / 3

Parallel Hash Left Join (cost=1,766,438.50..1,899,191.24 rows=53,590 width=184) (actual time=9,393.343..10,388.185 rows=11,467 loops=3)

  • Hash Cond: (br.id = rp2.booked_room_id)
6. 0.313 246.427 ↑ 3.4 540 3 / 3

Hash Left Join (cost=158.48..88,533.91 rows=1,828 width=199) (actual time=25.282..246.427 rows=540 loops=3)

  • Hash Cond: (brg.room_id = r.id)
7. 0.417 243.528 ↑ 3.4 540 3 / 3

Nested Loop Left Join (cost=40.36..88,410.99 rows=1,828 width=175) (actual time=22.664..243.528 rows=540 loops=3)

8. 0.355 237.714 ↑ 3.4 540 3 / 3

Hash Left Join (cost=39.93..74,763.61 rows=1,828 width=132) (actual time=22.602..237.714 rows=540 loops=3)

  • Hash Cond: (br.contract_id = ct.id)
9. 0.430 236.675 ↑ 3.4 540 3 / 3

Nested Loop (cost=0.42..74,717.24 rows=1,828 width=68) (actual time=21.853..236.675 rows=540 loops=3)

10. 230.309 230.309 ↑ 3.4 540 3 / 3

Parallel Seq Scan on booked_room_guest brg (cost=0.00..61,156.10 rows=1,828 width=24) (actual time=21.715..230.309 rows=540 loops=3)

  • Filter: ((actual_arrival_time IS NOT NULL) AND ((actual_arrival_time)::date = '2019-02-01'::date))
  • Rows Removed by Filter: 399,870
11. 5.936 5.936 ↑ 1.0 1 1,619 / 3

Index Scan using booked_room_1_pkey on booked_room br (cost=0.42..7.42 rows=1 width=52) (actual time=0.011..0.011 rows=1 loops=1,619)

  • Index Cond: (id = brg.booked_room_id)
12. 0.016 0.684 ↑ 20.9 11 3 / 3

Hash (cost=36.63..36.63 rows=230 width=80) (actual time=0.683..0.684 rows=11 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.038 0.668 ↑ 20.9 11 3 / 3

Hash Left Join (cost=23.73..36.63 rows=230 width=80) (actual time=0.657..0.668 rows=11 loops=3)

  • Hash Cond: (ct.company_id = cp.id)
14. 0.058 0.058 ↑ 20.9 11 3 / 3

Seq Scan on contract ct (cost=0.00..12.30 rows=230 width=56) (actual time=0.053..0.058 rows=11 loops=3)

15. 0.193 0.572 ↓ 1.0 623 3 / 3

Hash (cost=16.10..16.10 rows=610 width=40) (actual time=0.571..0.572 rows=623 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
16. 0.379 0.379 ↓ 1.0 623 3 / 3

Seq Scan on company cp (cost=0.00..16.10 rows=610 width=40) (actual time=0.032..0.379 rows=623 loops=3)

17. 5.397 5.397 ↑ 1.0 1 1,619 / 3

Index Scan using guest_pkey on guest g (cost=0.43..7.47 rows=1 width=59) (actual time=0.010..0.010 rows=1 loops=1,619)

  • Index Cond: (id = brg.guest_id)
18. 0.763 2.586 ↑ 1.0 2,902 3 / 3

Hash (cost=81.39..81.39 rows=2,939 width=40) (actual time=2.586..2.586 rows=2,902 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 169kB
19. 1.823 1.823 ↑ 1.0 2,902 3 / 3

Seq Scan on room r (cost=0.00..81.39 rows=2,939 width=40) (actual time=0.030..1.823 rows=2,902 loops=3)

20. 2,148.505 9,104.594 ↑ 1.2 6,053,029 3 / 3

Parallel Hash (cost=1,634,757.23..1,634,757.23 rows=7,566,223 width=12) (actual time=9,104.594..9,104.594 rows=6,053,029 loops=3)

  • Buckets: 262,144 Batches: 256 Memory Usage: 5,312kB
21. 6,956.089 6,956.089 ↑ 1.2 6,053,029 3 / 3

Parallel Seq Scan on req_package2 rp2 (cost=0.00..1,634,757.23 rows=7,566,223 width=12) (actual time=0.216..6,956.089 rows=6,053,029 loops=3)

Planning time : 4.646 ms
Execution time : 10,743.180 ms