explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4VIW

Settings
# exclusive inclusive rows x rows loops node
1. 4,715.676 91,252.266 ↑ 9.0 3,939,337 1

Finalize GroupAggregate (cost=7,774,435.43..13,303,893.04 rows=35,308,532 width=184) (actual time=68,605.369..91,252.266 rows=3,939,337 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. 4,078.913 86,536.590 ↑ 7.5 3,941,872 1

Gather Merge (cost=7,774,435.43..11,832,704.22 rows=29,423,776 width=184) (actual time=68,605.204..86,536.590 rows=3,941,872 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 9,363.971 82,457.677 ↑ 11.2 1,313,957 3 / 3

Partial GroupAggregate (cost=7,773,435.41..8,435,470.37 rows=14,711,888 width=184) (actual time=68,326.112..82,457.677 rows=1,313,957 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
4. 37,600.843 73,093.706 ↑ 1.1 13,673,115 3 / 3

Sort (cost=7,773,435.41..7,810,215.13 rows=14,711,888 width=184) (actual time=68,326.029..73,093.706 rows=13,673,115 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
  • Sort Method: external merge Disk: 1,328,568kB
  • Worker 0: Sort Method: external merge Disk: 1,178,776kB
  • Worker 1: Sort Method: external merge Disk: 1,234,920kB
5. 12,869.803 35,492.863 ↑ 1.1 13,673,115 3 / 3

Hash Left Join (cost=1,960,736.66..3,407,134.90 rows=14,711,888 width=184) (actual time=15,904.697..35,492.863 rows=13,673,115 loops=3)

  • Hash Cond: (br.contract_id = ct.id)
6. 8,602.493 22,622.304 ↑ 1.1 13,673,115 3 / 3

Parallel Hash Left Join (cost=1,960,697.16..3,168,027.21 rows=14,711,888 width=147) (actual time=15,903.838..22,622.304 rows=13,673,115 loops=3)

  • Hash Cond: (br.id = brg.booked_room_id)
7. 2,572.540 11,962.473 ↑ 1.2 5,423,912 3 / 3

Parallel Hash Left Join (cost=1,766,280.02..2,733,976.23 rows=6,775,805 width=64) (actual time=9,507.883..11,962.473 rows=5,423,912 loops=3)

  • Hash Cond: (br.id = rp2.booked_room_id)
8. 326.408 326.408 ↑ 1.3 184,114 3 / 3

Parallel Seq Scan on booked_room br (cost=0.00..83,450.15 rows=231,115 width=60) (actual time=0.076..326.408 rows=184,114 loops=3)

9. 2,233.820 9,063.525 ↑ 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,063.524..9,063.525 rows=6,053,029 loops=3)

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

11. 230.232 2,057.338 ↑ 1.3 400,410 3 / 3

Parallel Hash (cost=180,793.55..180,793.55 rows=501,807 width=91) (actual time=2,057.337..2,057.338 rows=400,410 loops=3)

  • Buckets: 65,536 Batches: 32 Memory Usage: 4,096kB
12. 109.030 1,827.106 ↑ 1.3 400,410 3 / 3

Hash Left Join (cost=108,694.99..180,793.55 rows=501,807 width=91) (actual time=1,287.054..1,827.106 rows=400,410 loops=3)

  • Hash Cond: (brg.room_id = r.id)
13. 609.204 1,715.303 ↑ 1.3 400,410 3 / 3

Parallel Hash Left Join (cost=108,576.86..179,358.17 rows=501,807 width=67) (actual time=1,284.226..1,715.303 rows=400,410 loops=3)

  • Hash Cond: (brg.guest_id = g.id)
14. 316.796 316.796 ↑ 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.022..316.796 rows=400,410 loops=3)

15. 216.202 789.303 ↑ 1.2 369,772 3 / 3

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

  • Buckets: 131,072 Batches: 16 Memory Usage: 7,552kB
16. 573.101 573.101 ↑ 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.100..573.101 rows=369,772 loops=3)

17. 0.762 2.773 ↑ 1.0 2,902 3 / 3

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

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

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

19. 0.013 0.756 ↑ 20.9 11 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.035 0.743 ↑ 20.9 11 3 / 3

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

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

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

22. 0.188 0.618 ↓ 1.0 623 3 / 3

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

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

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

Planning time : 18.435 ms
Execution time : 98,321.180 ms