explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u1Sf

Settings
# exclusive inclusive rows x rows loops node
1. 1,116.826 5,643.764 ↑ 1.0 903,695 1

Finalize GroupAggregate (cost=357,252.72..497,669.82 rows=914,895 width=168) (actual time=3,021.957..5,643.764 rows=903,695 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, ct.title
2. 650.941 4,526.938 ↓ 1.2 908,054 1

Gather Merge (cost=357,252.72..461,455.24 rows=762,412 width=168) (actual time=3,021.928..4,526.938 rows=908,054 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 614.248 3,875.997 ↑ 1.3 302,685 3 / 3

Partial GroupAggregate (cost=356,252.70..372,453.95 rows=381,206 width=168) (actual time=3,007.912..3,875.997 rows=302,685 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, ct.title
4. 594.831 3,261.749 ↑ 1.3 303,453 3 / 3

Sort (cost=356,252.70..357,205.71 rows=381,206 width=168) (actual time=3,007.867..3,261.749 rows=303,453 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, ct.title
  • Sort Method: external merge Disk: 22,632kB
  • Worker 0: Sort Method: external merge Disk: 21,384kB
  • Worker 1: Sort Method: external merge Disk: 18,200kB
5. 311.307 2,666.918 ↑ 1.3 303,453 3 / 3

Hash Left Join (cost=176,595.14..289,642.00 rows=381,206 width=168) (actual time=1,859.990..2,666.918 rows=303,453 loops=3)

  • Hash Cond: (brg.room_id = r.id)
6. 95.333 2,352.960 ↑ 1.3 303,453 3 / 3

Hash Left Join (cost=176,477.02..283,758.12 rows=381,206 width=163) (actual time=1,857.291..2,352.960 rows=303,453 loops=3)

  • Hash Cond: (br.contract_id = ct.id)
7. 510.315 2,257.003 ↑ 1.3 303,453 3 / 3

Parallel Hash Left Join (cost=176,437.51..282,289.08 rows=381,206 width=107) (actual time=1,856.636..2,257.003 rows=303,453 loops=3)

  • Hash Cond: (brg.guest_id = g.id)
8. 287.579 1,002.936 ↑ 1.3 303,453 3 / 3

Parallel Hash Left Join (cost=67,860.65..159,586.56 rows=381,206 width=64) (actual time=773.479..1,002.936 rows=303,453 loops=3)

  • Hash Cond: (br.id = brg.booked_room_id)
9. 289.912 289.912 ↑ 1.3 139,829 3 / 3

Parallel Seq Scan on booked_room br (cost=0.00..83,450.15 rows=175,571 width=48) (actual time=0.028..289.912 rows=139,829 loops=3)

  • Filter: (actual_arrival_time IS NOT NULL)
  • Rows Removed by Filter: 44,285
10. 131.473 425.445 ↑ 1.3 400,410 3 / 3

Parallel Hash (cost=58,647.07..58,647.07 rows=501,807 width=24) (actual time=425.444..425.445 rows=400,410 loops=3)

  • Buckets: 131,072 Batches: 16 Memory Usage: 4,608kB
11. 293.972 293.972 ↑ 1.3 400,410 3 / 3

Parallel Seq Scan on booked_room_guest brg (cost=0.00..58,647.07 rows=501,807 width=24) (actual time=0.021..293.972 rows=400,410 loops=3)

12. 189.366 743.752 ↑ 1.2 369,772 3 / 3

Parallel Hash (cost=97,900.27..97,900.27 rows=459,327 width=59) (actual time=743.752..743.752 rows=369,772 loops=3)

  • Buckets: 131,072 Batches: 16 Memory Usage: 7,552kB
13. 554.386 554.386 ↑ 1.2 369,772 3 / 3

Parallel Seq Scan on guest g (cost=0.00..97,900.27 rows=459,327 width=59) (actual time=0.064..554.386 rows=369,772 loops=3)

14. 0.010 0.624 ↑ 20.9 11 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.019 0.614 ↑ 20.9 11 3 / 3

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

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

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

17. 0.168 0.550 ↓ 1.0 623 3 / 3

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

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

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

19. 0.757 2.651 ↑ 1.0 2,902 3 / 3

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

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

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

Planning time : 1.698 ms
Execution time : 5,964.842 ms