explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jtqd

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 49,268.385 ↑ 6.6 2,395,393 1

Finalize GroupAggregate (cost=4,432,245.91..6,965,163.41 rows=15,857,593 width=184) (actual time=37,505.849..49,268.385 rows=2,395,393 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, rp2.sum, ct.request_id
2. 6,922.845 50,001.722 ↑ 5.4 2,427,921 1

Gather Merge (cost=4,432,245.91..6,271,393.73 rows=13,214,660 width=184) (actual time=37,505.824..50,001.722 rows=2,427,921 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 3,768.823 43,078.877 ↑ 8.2 809,307 3 / 3

Partial GroupAggregate (cost=4,431,245.88..4,745,094.06 rows=6,607,330 width=184) (actual time=36,950.001..43,078.877 rows=809,307 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, rp2.sum, ct.request_id
4. 12,118.628 39,310.054 ↑ 1.2 5,321,591 3 / 3

Sort (cost=4,431,245.88..4,447,764.21 rows=6,607,330 width=184) (actual time=36,949.905..39,310.054 rows=5,321,591 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, rp2.sum, ct.request_id
  • Sort Method: external merge Disk: 338,128kB
  • Worker 0: Sort Method: external merge Disk: 440,680kB
  • Worker 1: Sort Method: external merge Disk: 406,680kB
5. 4,942.994 27,191.426 ↑ 1.2 5,321,591 3 / 3

Hash Left Join (cost=307,644.20..2,508,424.57 rows=6,607,330 width=184) (actual time=19,164.974..27,191.426 rows=5,321,591 loops=3)

  • Hash Cond: (br.contract_id = ct.id)
6. 4,882.933 22,247.723 ↑ 1.2 5,321,591 3 / 3

Parallel Hash Left Join (cost=307,604.70..2,401,015.94 rows=6,607,330 width=139) (actual time=19,164.172..22,247.723 rows=5,321,591 loops=3)

  • Hash Cond: (rp2.booked_room_guest_id = brg.id)
7. 3,905.789 15,420.640 ↑ 1.2 5,321,591 3 / 3

Parallel Hash Left Join (cost=113,187.56..2,007,859.79 rows=6,607,330 width=64) (actual time=13,155.949..15,420.640 rows=5,321,591 loops=3)

  • Hash Cond: (rp2.booked_room_id = br.id)
8. 3,861.104 11,129.181 ↑ 1.2 5,321,591 3 / 3

Parallel Hash Left Join (cost=24,591.47..1,822,232.33 rows=6,607,330 width=20) (actual time=9,151.726..11,129.181 rows=5,321,591 loops=3)

  • Hash Cond: (rp2.request_id = p2.request_id)
9. 7,015.222 7,015.222 ↑ 1.2 5,321,576 3 / 3

Parallel Seq Scan on req_package2 rp2 (cost=0.00..1,653,672.79 rows=6,607,330 width=28) (actual time=0.098..7,015.222 rows=5,321,576 loops=3)

  • Filter: (state = 3)
  • Rows Removed by Filter: 731,453
10. 81.681 252.855 ↑ 1.3 271,111 3 / 3

Parallel Hash (cost=19,031.10..19,031.10 rows=338,910 width=8) (actual time=252.853..252.855 rows=271,111 loops=3)

  • Buckets: 262,144 Batches: 8 Memory Usage: 6,080kB
11. 171.174 171.174 ↑ 1.3 271,111 3 / 3

Parallel Seq Scan on payment2 p2 (cost=0.00..19,031.10 rows=338,910 width=8) (actual time=0.046..171.174 rows=271,111 loops=3)

12. 60.421 385.670 ↑ 1.3 184,114 3 / 3

Parallel Hash (cost=83,450.15..83,450.15 rows=231,115 width=52) (actual time=385.670..385.670 rows=184,114 loops=3)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,776kB
13. 325.249 325.249 ↑ 1.3 184,114 3 / 3

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

14. 216.518 1,944.150 ↑ 1.3 400,410 3 / 3

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

  • Buckets: 65,536 Batches: 32 Memory Usage: 4,064kB
15. 113.675 1,727.632 ↑ 1.3 400,410 3 / 3

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

  • Hash Cond: (brg.room_id = r.id)
16. 560.214 1,610.158 ↑ 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,202.237..1,610.158 rows=400,410 loops=3)

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

18. 200.493 748.487 ↑ 1.2 369,772 3 / 3

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

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

20. 1.220 3.799 ↑ 1.0 2,902 3 / 3

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

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

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

22. 0.013 0.709 ↑ 20.9 11 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.040 0.696 ↑ 20.9 11 3 / 3

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

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

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

25. 0.174 0.579 ↓ 1.0 623 3 / 3

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

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

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

Planning time : 9.987 ms
Execution time : 56,517.223 ms