explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wN7j

Settings
# exclusive inclusive rows x rows loops node
1. 20.477 20,084.623 ↑ 33.6 5,258 1

GroupAggregate (cost=1,943,639.55..1,951,584.39 rows=176,552 width=184) (actual time=20,059.879..20,084.623 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
2. 59.960 20,064.146 ↑ 5.1 34,400 1

Sort (cost=1,943,639.55..1,944,080.93 rows=176,552 width=184) (actual time=20,059.840..20,064.146 rows=34,400 loops=1)

  • 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
  • Sort Method: quicksort Memory: 6,002kB
3. 2,659.822 20,004.186 ↑ 5.1 34,400 1

Hash Right Join (cost=99,809.99..1,912,562.78 rows=176,552 width=184) (actual time=975.709..20,004.186 rows=34,400 loops=1)

  • Hash Cond: (rp2.booked_room_id = br.id)
4. 17,009.592 17,009.592 ↓ 1.0 18,159,087 1

Seq Scan on req_package2 rp2 (cost=0.00..1,740,684.36 rows=18,158,936 width=12) (actual time=0.020..17,009.592 rows=18,159,087 loops=1)

5. 0.595 334.772 ↑ 3.7 1,619 1

Hash (cost=99,734.71..99,734.71 rows=6,022 width=199) (actual time=334.772..334.772 rows=1,619 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 235kB
6. 14.976 334.177 ↑ 3.7 1,619 1

Gather (cost=1,158.48..99,734.71 rows=6,022 width=199) (actual time=55.122..334.177 rows=1,619 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 0.380 319.201 ↑ 4.6 540 3 / 3

Hash Left Join (cost=158.48..98,132.51 rows=2,509 width=199) (actual time=61.583..319.201 rows=540 loops=3)

  • Hash Cond: (brg.room_id = r.id)
8. 0.705 314.465 ↑ 4.6 540 3 / 3

Nested Loop Left Join (cost=40.36..98,007.80 rows=2,509 width=175) (actual time=57.164..314.465 rows=540 loops=3)

9. 0.585 304.586 ↑ 4.6 540 3 / 3

Hash Left Join (cost=39.93..79,552.44 rows=2,509 width=132) (actual time=57.041..304.586 rows=540 loops=3)

  • Hash Cond: (br.contract_id = ct.id)
10. 0.413 302.900 ↑ 4.6 540 3 / 3

Nested Loop (cost=0.42..79,503.52 rows=2,509 width=76) (actual time=55.647..302.900 rows=540 loops=3)

11. 295.471 295.471 ↑ 4.6 540 3 / 3

Parallel Seq Scan on booked_room_guest brg (cost=0.00..61,156.10 rows=2,509 width=24) (actual time=55.506..295.471 rows=540 loops=3)

  • Filter: ((actual_arrival_time)::date = '2019-02-01'::date)
  • Rows Removed by Filter: 399,870
12. 7.016 7.016 ↑ 1.0 1 1,619 / 3

Index Scan using booked_room_1_pkey on booked_room br (cost=0.42..7.31 rows=1 width=60) (actual time=0.013..0.013 rows=1 loops=1,619)

  • Index Cond: (id = brg.booked_room_id)
13. 0.012 1.101 ↑ 20.9 11 3 / 3

Hash (cost=36.63..36.63 rows=230 width=72) (actual time=1.101..1.101 rows=11 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.049 1.089 ↑ 20.9 11 3 / 3

Hash Left Join (cost=23.73..36.63 rows=230 width=72) (actual time=1.072..1.089 rows=11 loops=3)

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

Seq Scan on contract ct (cost=0.00..12.30 rows=230 width=48) (actual time=0.042..0.049 rows=11 loops=3)

16. 0.363 0.991 ↓ 1.0 623 3 / 3

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

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

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

18. 9.174 9.174 ↑ 1.0 1 1,619 / 3

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

  • Index Cond: (id = brg.guest_id)
19. 1.378 4.356 ↑ 1.0 2,902 3 / 3

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

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

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

Planning time : 19.332 ms
Execution time : 20,086.974 ms