explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OrMy

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

Finalize GroupAggregate (cost=1,915,046.30..1,936,470.57 rows=134,129 width=184) (actual time=10,390.269..10,409.408 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. 291.471 10,675.011 ↑ 21.3 5,258 1

Gather Merge (cost=1,915,046.30..1,930,602.43 rows=111,774 width=184) (actual time=10,390.227..10,675.011 rows=5,258 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 7.226 10,383.540 ↑ 31.9 1,753 3 / 3

Partial GroupAggregate (cost=1,914,046.28..1,916,700.91 rows=55,887 width=184) (actual time=10,374.994..10,383.540 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. 17.053 10,376.314 ↑ 4.9 11,467 3 / 3

Sort (cost=1,914,046.28..1,914,186.00 rows=55,887 width=184) (actual time=10,374.947..10,376.314 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,087kB
  • Worker 0: Sort Method: quicksort Memory: 1,989kB
  • Worker 1: Sort Method: quicksort Memory: 1,904kB
5. 1,037.610 10,359.261 ↑ 4.9 11,467 3 / 3

Parallel Hash Left Join (cost=1,766,438.50..1,904,669.53 rows=55,887 width=184) (actual time=9,362.666..10,359.261 rows=11,467 loops=3)

  • Hash Cond: (br.id = rp2.booked_room_id)
6. 0.316 258.758 ↑ 3.5 540 3 / 3

Hash Left Join (cost=158.48..93,693.20 rows=1,906 width=199) (actual time=27.846..258.758 rows=540 loops=3)

  • Hash Cond: (brg.room_id = r.id)
7. 0.496 255.473 ↑ 3.5 540 3 / 3

Nested Loop Left Join (cost=40.36..93,570.07 rows=1,906 width=175) (actual time=24.859..255.473 rows=540 loops=3)

8. 0.327 249.580 ↑ 3.5 540 3 / 3

Hash Left Join (cost=39.93..79,550.18 rows=1,906 width=132) (actual time=24.811..249.580 rows=540 loops=3)

  • Hash Cond: (br.contract_id = ct.id)
9. 0.503 248.547 ↑ 3.5 540 3 / 3

Nested Loop (cost=0.42..79,503.52 rows=1,906 width=68) (actual time=24.069..248.547 rows=540 loops=3)

10. 242.108 242.108 ↑ 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=23.961..242.108 rows=540 loops=3)

  • Filter: ((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.31 rows=1 width=52) (actual time=0.011..0.011 rows=1 loops=1,619)

  • Index Cond: (id = brg.booked_room_id)
  • Filter: (actual_arrival_time IS NOT NULL)
12. 0.009 0.706 ↑ 20.9 11 3 / 3

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

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

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

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

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

15. 0.183 0.625 ↓ 1.0 623 3 / 3

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

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

Seq Scan on company cp (cost=0.00..16.10 rows=610 width=40) (actual time=0.029..0.442 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.36 rows=1 width=59) (actual time=0.010..0.010 rows=1 loops=1,619)

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

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

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

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

20. 2,092.667 9,062.893 ↑ 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,062.893..9,062.893 rows=6,053,029 loops=3)

  • Buckets: 262,144 Batches: 256 Memory Usage: 5,312kB
21. 6,970.226 6,970.226 ↑ 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.123..6,970.226 rows=6,053,029 loops=3)

Planning time : 7.391 ms
Execution time : 10,685.972 ms