explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 58Oe

Settings
# exclusive inclusive rows x rows loops node
1. 99.673 11,363.301 ↑ 34.1 324 1

Gather (cost=36,949.50..90,753.75 rows=11,046 width=48) (actual time=4,473.286..11,363.301 rows=324 loops=1)

  • Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=28,935 read=3,685, temp read=20,817 written=20,631
2. 412.027 11,263.628 ↑ 42.6 108 3 / 3

Hash Semi Join (cost=35,949.50..88,649.15 rows=4,602 width=48) (actual time=4,367.509..11,263.628 rows=108 loops=3)

  • Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id
  • Hash Cond: (branch_bills.branch_id = b.id)
  • Buffers: shared hit=28,935 read=3,685, temp read=20,817 written=20,631
  • Worker 0: actual time=4,870.659..11263.040 rows=94 loops=1
  • Buffers: shared hit=9,915 read=1,038, temp read=6,965 written=6,903
  • Worker 1: actual time=3,759.329..11258.789 rows=145 loops=1
  • Buffers: shared hit=9,561 read=1,326, temp read=6,933 written=6,871
3. 1,509.523 10,851.429 ↑ 1.2 360,000 3 / 3

Hash Join (cost=35,859.32..87,326.53 rows=450,000 width=56) (actual time=3,230.721..10,851.429 rows=360,000 loops=3)

  • Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id, tbl_branches.id
  • Inner Unique: true
  • Hash Cond: (branch_bills.branch_id = tbl_branches.id)
  • Buffers: shared hit=28,791 read=3,685, temp read=20,817 written=20,631
  • Worker 0: actual time=3,260.305..10935.413 rows=364,470 loops=1
  • Buffers: shared hit=9,849 read=1,038, temp read=6,965 written=6,903
  • Worker 1: actual time=3,263.955..11015.922 rows=359,046 loops=1
  • Buffers: shared hit=9,495 read=1,326, temp read=6,933 written=6,871
4. 4,376.278 9,338.230 ↑ 1.2 360,000 3 / 3

Hash Join (cost=35,541.32..85,826.78 rows=450,000 width=48) (actual time=3,226.992..9,338.230 rows=360,000 loops=3)

  • Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id
  • Hash Cond: (bills.id = branch_bills.bill_id)
  • Buffers: shared hit=28,512 read=3,685, temp read=20,817 written=20,631
  • Worker 0: actual time=3,256.865..9171.739 rows=364,470 loops=1
  • Buffers: shared hit=9,756 read=1,038, temp read=6,965 written=6,903
  • Worker 1: actual time=3,260.437..9250.856 rows=359,046 loops=1
  • Buffers: shared hit=9,402 read=1,326, temp read=6,933 written=6,871
5. 911.579 1,735.715 ↑ 1.2 360,000 3 / 3

Merge Left Join (cost=129.32..31,921.28 rows=450,000 width=40) (actual time=0.036..1,735.715 rows=360,000 loops=3)

  • Output: bills.id, bills.bill_date, bills.bill_number, company_bills.company_id
  • Merge Cond: (bills.id = company_bills.bill_id)
  • Buffers: shared hit=14,635
  • Worker 0: actual time=0.039..1793.861 rows=364,470 loops=1
  • Buffers: shared hit=4,932
  • Worker 1: actual time=0.049..1618.021 rows=359,046 loops=1
  • Buffers: shared hit=4,866
6. 824.119 824.119 ↑ 1.2 360,000 3 / 3

Parallel Index Scan using tbl_bills_pkey on public.tbl_bills bills (cost=0.43..30,650.43 rows=450,000 width=32) (actual time=0.017..824.119 rows=360,000 loops=3)

  • Output: bills.id, bills.bill_date, bills.bill_number
  • Buffers: shared hit=14,635
  • Worker 0: actual time=0.018..962.541 rows=364,470 loops=1
  • Buffers: shared hit=4,932
  • Worker 1: actual time=0.022..797.369 rows=359,046 loops=1
  • Buffers: shared hit=4,866
7. 0.009 0.017 ↓ 0.0 0 3 / 3

Sort (cost=128.89..133.52 rows=1,850 width=16) (actual time=0.017..0.017 rows=0 loops=3)

  • Output: company_bills.company_id, company_bills.bill_id
  • Sort Key: company_bills.bill_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: actual time=0.019..0.019 rows=0 loops=1
  • Worker 1: actual time=0.025..0.025 rows=0 loops=1
8. 0.008 0.008 ↓ 0.0 0 3 / 3

Seq Scan on public.tbl_company_bills company_bills (cost=0.00..28.50 rows=1,850 width=16) (actual time=0.008..0.008 rows=0 loops=3)

  • Output: company_bills.company_id, company_bills.bill_id
  • Worker 0: actual time=0.009..0.010 rows=0 loops=1
  • Worker 1: actual time=0.013..0.013 rows=0 loops=1
9. 2,185.740 3,226.237 ↑ 1.0 1,080,000 3 / 3

Hash (cost=16,638.00..16,638.00 rows=1,080,000 width=16) (actual time=3,226.237..3,226.237 rows=1,080,000 loops=3)

  • Output: branch_bills.branch_id, branch_bills.bill_id
  • Buckets: 131,072 Batches: 32 Memory Usage: 2,614kB
  • Buffers: shared hit=13,829 read=3,685, temp written=13,740
  • Worker 0: actual time=3,256.221..3256.221 rows=1,080,000 loops=1
  • Buffers: shared hit=4,800 read=1,038, temp written=4,580
  • Worker 1: actual time=3,259.710..3259.710 rows=1,080,000 loops=1
  • Buffers: shared hit=4,512 read=1,326, temp written=4,580
10. 1,040.497 1,040.497 ↑ 1.0 1,080,000 3 / 3

Seq Scan on public.tbl_branch_bills branch_bills (cost=0.00..16,638.00 rows=1,080,000 width=16) (actual time=0.159..1,040.497 rows=1,080,000 loops=3)

  • Output: branch_bills.branch_id, branch_bills.bill_id
  • Buffers: shared hit=13,829 read=3,685
  • Worker 0: actual time=0.039..1191.283 rows=1,080,000 loops=1
  • Buffers: shared hit=4,800 read=1,038
  • Worker 1: actual time=0.030..1115.410 rows=1,080,000 loops=1
  • Buffers: shared hit=4,512 read=1,326
11. 1.976 3.676 ↑ 1.0 10,000 3 / 3

Hash (cost=193.00..193.00 rows=10,000 width=8) (actual time=3.676..3.676 rows=10,000 loops=3)

  • Output: tbl_branches.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 519kB
  • Buffers: shared hit=279
  • Worker 0: actual time=3.354..3.354 rows=10,000 loops=1
  • Buffers: shared hit=93
  • Worker 1: actual time=3.456..3.456 rows=10,000 loops=1
  • Buffers: shared hit=93
12. 1.700 1.700 ↑ 1.0 10,000 3 / 3

Seq Scan on public.tbl_branches (cost=0.00..193.00 rows=10,000 width=8) (actual time=0.009..1.700 rows=10,000 loops=3)

  • Output: tbl_branches.id
  • Buffers: shared hit=279
  • Worker 0: actual time=0.012..1.568 rows=10,000 loops=1
  • Buffers: shared hit=93
  • Worker 1: actual time=0.008..1.598 rows=10,000 loops=1
  • Buffers: shared hit=93
13. 0.004 0.172 ↑ 34.0 3 3 / 3

Hash (cost=88.90..88.90 rows=102 width=16) (actual time=0.171..0.172 rows=3 loops=3)

  • Output: b.id, r.branch_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=60
  • Worker 0: actual time=0.222..0.222 rows=3 loops=1
  • Buffers: shared hit=24
  • Worker 1: actual time=0.200..0.200 rows=3 loops=1
  • Buffers: shared hit=24
14. 0.001 0.168 ↑ 34.0 3 3 / 3

Nested Loop (cost=77.92..88.90 rows=102 width=16) (actual time=0.161..0.168 rows=3 loops=3)

  • Output: b.id, r.branch_id
  • Buffers: shared hit=60
  • Worker 0: actual time=0.209..0.217 rows=3 loops=1
  • Buffers: shared hit=24
  • Worker 1: actual time=0.190..0.196 rows=3 loops=1
  • Buffers: shared hit=24
15. 0.005 0.137 ↑ 34.0 3 3 / 3

Merge Join (cost=77.77..79.45 rows=102 width=24) (actual time=0.133..0.137 rows=3 loops=3)

  • Output: b.id, r.branch_id, r.user_id
  • Merge Cond: (b.id = r.branch_id)
  • Buffers: shared hit=46
  • Worker 0: actual time=0.167..0.172 rows=3 loops=1
  • Buffers: shared hit=18
  • Worker 1: actual time=0.154..0.157 rows=3 loops=1
  • Buffers: shared hit=18
16. 0.035 0.035 ↑ 1,666.7 6 3 / 3

Index Only Scan using tbl_branches_pkey on public.tbl_branches b (cost=0.29..366.29 rows=10,000 width=8) (actual time=0.034..0.035 rows=6 loops=3)

  • Output: b.id
  • Heap Fetches: 6
  • Buffers: shared hit=11
  • Worker 0: actual time=0.053..0.055 rows=6 loops=1
  • Buffers: shared hit=4
  • Worker 1: actual time=0.033..0.035 rows=6 loops=1
  • Buffers: shared hit=4
17. 0.019 0.097 ↑ 34.0 3 3 / 3

Sort (cost=77.48..77.74 rows=102 width=16) (actual time=0.096..0.097 rows=3 loops=3)

  • Output: r.branch_id, r.user_id
  • Sort Key: r.branch_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=35
  • Worker 0: actual time=0.112..0.113 rows=3 loops=1
  • Buffers: shared hit=14
  • Worker 1: actual time=0.118..0.118 rows=3 loops=1
  • Buffers: shared hit=14
18. 0.011 0.078 ↑ 34.0 3 3 / 3

Hash Join (cost=43.44..74.08 rows=102 width=16) (actual time=0.074..0.078 rows=3 loops=3)

  • Output: r.branch_id, r.user_id
  • Hash Cond: (r.group_id = g.id)
  • Buffers: shared hit=27
  • Worker 0: actual time=0.085..0.089 rows=3 loops=1
  • Buffers: shared hit=10
  • Worker 1: actual time=0.087..0.093 rows=3 loops=1
  • Buffers: shared hit=10
19. 0.014 0.014 ↑ 360.0 3 3 / 3

Seq Scan on public.tbl_rules r (cost=0.00..26.20 rows=1,080 width=24) (actual time=0.011..0.014 rows=3 loops=3)

  • Output: r.id, r.rule_type, r.user_id, r.group_id, r.company_id, r.branch_id
  • Filter: ((r.user_id = 1) AND ((r.rule_type)::text = 'BRANCH'::text))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=3
  • Worker 0: actual time=0.015..0.017 rows=3 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.013..0.017 rows=3 loops=1
  • Buffers: shared hit=1
20. 0.002 0.053 ↑ 13.0 1 3 / 3

Hash (cost=43.28..43.28 rows=13 width=16) (actual time=0.053..0.053 rows=1 loops=3)

  • Output: g.id, gp.group_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=24
  • Worker 0: actual time=0.060..0.060 rows=1 loops=1
  • Buffers: shared hit=9
  • Worker 1: actual time=0.061..0.061 rows=1 loops=1
  • Buffers: shared hit=9
21. 0.001 0.051 ↑ 13.0 1 3 / 3

Nested Loop (cost=22.32..43.28 rows=13 width=16) (actual time=0.049..0.051 rows=1 loops=3)

  • Output: g.id, gp.group_id
  • Inner Unique: true
  • Buffers: shared hit=24
  • Worker 0: actual time=0.055..0.057 rows=1 loops=1
  • Buffers: shared hit=9
  • Worker 1: actual time=0.056..0.058 rows=1 loops=1
  • Buffers: shared hit=9
22. 0.004 0.035 ↑ 13.0 1 3 / 3

Nested Loop (cost=22.17..40.82 rows=13 width=8) (actual time=0.033..0.035 rows=1 loops=3)

  • Output: gp.group_id
  • Buffers: shared hit=16
  • Worker 0: actual time=0.032..0.034 rows=1 loops=1
  • Buffers: shared hit=6
  • Worker 1: actual time=0.037..0.038 rows=1 loops=1
  • Buffers: shared hit=6
23. 0.021 0.021 ↑ 1.0 1 3 / 3

Index Scan using tbl_permissions_name_key on public.tbl_permissions p (cost=0.14..8.16 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=3)

  • Output: p.id, p.name
  • Index Cond: ((p.name)::text = 'Permission W'::text)
  • Buffers: shared hit=8
  • Worker 0: actual time=0.021..0.021 rows=1 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.026..0.026 rows=1 loops=1
  • Buffers: shared hit=3
24. 0.006 0.010 ↑ 9.0 1 3 / 3

Bitmap Heap Scan on public.tbl_group_permissions gp (cost=22.03..32.57 rows=9 width=16) (actual time=0.009..0.010 rows=1 loops=3)

  • Output: gp.group_id, gp.permission_id
  • Recheck Cond: (gp.permission_id = p.id)
  • Heap Blocks: exact=1
  • Buffers: shared hit=8
  • Worker 0: actual time=0.009..0.010 rows=1 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.008..0.009 rows=1 loops=1
  • Buffers: shared hit=3
25. 0.004 0.004 ↑ 9.0 1 3 / 3

Bitmap Index Scan on tbl_group_permissions_pkey (cost=0.00..22.03 rows=9 width=0) (actual time=0.004..0.004 rows=1 loops=3)

  • Index Cond: (gp.permission_id = p.id)
  • Buffers: shared hit=5
  • Worker 0: actual time=0.003..0.003 rows=1 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.002..0.002 rows=1 loops=1
  • Buffers: shared hit=2
26. 0.015 0.015 ↑ 1.0 1 3 / 3

Index Only Scan using tbl_groups_pkey on public.tbl_groups g (cost=0.14..0.19 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=3)

  • Output: g.id
  • Index Cond: (g.id = gp.group_id)
  • Heap Fetches: 1
  • Buffers: shared hit=8
  • Worker 0: actual time=0.021..0.022 rows=1 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.019..0.019 rows=1 loops=1
  • Buffers: shared hit=3
27. 0.004 0.030 ↑ 1.0 1 9 / 3

Materialize (cost=0.15..8.18 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=9)

  • Output: u.id
  • Buffers: shared hit=14
  • Worker 0: actual time=0.014..0.014 rows=1 loops=3
  • Buffers: shared hit=6
  • Worker 1: actual time=0.012..0.012 rows=1 loops=3
  • Buffers: shared hit=6
28. 0.026 0.026 ↑ 1.0 1 3 / 3

Index Only Scan using tbl_users_pkey on public.tbl_users u (cost=0.15..8.17 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=3)

  • Output: u.id
  • Index Cond: (u.id = 1)
  • Heap Fetches: 1
  • Buffers: shared hit=14
  • Worker 0: actual time=0.038..0.039 rows=1 loops=1
  • Buffers: shared hit=6
  • Worker 1: actual time=0.033..0.034 rows=1 loops=1
  • Buffers: shared hit=6
Planning time : 2.714 ms
Execution time : 11,363.526 ms