explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9VDe

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 363.672 ↑ 1.0 12 1

Limit (cost=84,910.54..84,911.94 rows=12 width=53) (actual time=363.577..363.672 rows=12 loops=1)

  • Output: inv.id, inv.invoice_date, inv.invoice_xid, inv.invoice_type, brs.branch_id, cinvs.company_id, br.name
  • Buffers: shared hit=12679 read=19834, temp read=5917 written=6376
2. 25.787 363.659 ↑ 24,375.0 12 1

Gather Merge (cost=84,910.54..119,037.92 rows=292,500 width=53) (actual time=363.576..363.659 rows=12 loops=1)

  • Output: inv.id, inv.invoice_date, inv.invoice_xid, inv.invoice_type, brs.branch_id, cinvs.company_id, br.name
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=12679 read=19834, temp read=5917 written=6376
3. 0.145 337.872 ↑ 988.2 148 3 / 3

Sort (cost=83,910.51..84,276.14 rows=146,250 width=53) (actual time=337.864..337.872 rows=148 loops=3)

  • Output: inv.id, inv.invoice_date, inv.invoice_xid, inv.invoice_type, brs.branch_id, cinvs.company_id, br.name
  • Sort Key: inv.invoice_date DESC, br.name
  • Sort Method: quicksort Memory: 95kB
  • Buffers: shared hit=12679 read=19834, temp read=5917 written=6376
  • Worker 0: actual time=341.902..341.924 rows=436 loops=1
  • Buffers: shared hit=4015 read=781, temp read=1398 written=1378
  • Worker 1: actual time=309.749..309.749 rows=2 loops=1
  • Buffers: shared hit=4133 read=529, temp read=783 written=1282
4. 0.099 337.727 ↑ 468.8 312 3 / 3

Hash Join (cost=23,454.23..66,362.17 rows=146,250 width=53) (actual time=206.037..337.727 rows=312 loops=3)

  • Output: inv.id, inv.invoice_date, inv.invoice_xid, inv.invoice_type, brs.branch_id, cinvs.company_id, br.name
  • Inner Unique: true
  • Hash Cond: (brs.branch_id = br.id)
  • Buffers: shared hit=12649 read=19834, temp read=5917 written=6376
  • Worker 0: actual time=226.499..341.718 rows=436 loops=1
  • Buffers: shared hit=4000 read=781, temp read=1398 written=1378
  • Worker 1: actual time=227.847..309.704 rows=2 loops=1
  • Buffers: shared hit=4118 read=529, temp read=783 written=1282
5. 16.877 337.283 ↑ 468.8 312 3 / 3

Hash Left Join (cost=23,377.73..65,900.14 rows=146,250 width=43) (actual time=205.663..337.283 rows=312 loops=3)

  • Output: inv.id, inv.invoice_date, inv.invoice_xid, inv.invoice_type, brs.branch_id, cinvs.company_id
  • Hash Cond: (inv.id = cinvs.invoice_id)
  • Buffers: shared hit=12455 read=19834, temp read=5917 written=6376
  • Worker 0: actual time=226.129..341.250 rows=436 loops=1
  • Buffers: shared hit=3930 read=781, temp read=1398 written=1378
  • Worker 1: actual time=227.410..309.265 rows=2 loops=1
  • Buffers: shared hit=4048 read=529, temp read=783 written=1282
6. 98.837 232.214 ↑ 468.8 312 3 / 3

Hash Join (cost=15,704.73..53,977.16 rows=146,250 width=35) (actual time=117.174..232.214 rows=312 loops=3)

  • Output: inv.id, inv.invoice_date, inv.invoice_xid, inv.invoice_type, brs.branch_id
  • Hash Cond: (inv.id = brs.invoice_id)
  • Buffers: shared hit=8660 read=19834, temp read=4095 written=4053
  • Worker 0: actual time=122.960..216.885 rows=436 loops=1
  • Buffers: shared hit=2665 read=781, temp read=617 written=603
  • Worker 1: actual time=119.459..192.174 rows=2 loops=1
  • Buffers: shared hit=2783 read=529, temp read=523 written=509
7. 32.920 32.920 ↑ 2.0 262,077 3 / 3

Parallel Seq Scan on "slow-query".invoices inv (cost=0.00..25,979.95 rows=523,195 width=27) (actual time=0.017..32.920 rows=262,077 loops=3)

  • Output: inv.id, inv.invoice_date, inv.invoice_xid, inv.invoice_type
  • Buffers: shared hit=914 read=19834
  • Worker 0: actual time=0.004..26.466 rows=115539 loops=1
  • Buffers: shared hit=70 read=781
  • Worker 1: actual time=0.026..9.197 rows=97377 loops=1
  • Buffers: shared hit=188 read=529
8. 0.378 100.457 ↑ 375.0 936 3 / 3

Hash (cost=9,603.23..9,603.23 rows=351,000 width=16) (actual time=100.457..100.457 rows=936 loops=3)

  • Output: brs.branch_id, brs.invoice_id
  • Buckets: 131072 Batches: 8 Memory Usage: 1030kB
  • Buffers: shared hit=7696
  • Worker 0: actual time=120.612..120.612 rows=936 loops=1
  • Buffers: shared hit=2570
  • Worker 1: actual time=118.506..118.506 rows=936 loops=1
  • Buffers: shared hit=2570
9. 99.880 100.079 ↑ 375.0 936 3 / 3

Seq Scan on "slow-query".branch_invoices brs (cost=53.23..9,603.23 rows=351,000 width=16) (actual time=0.275..100.079 rows=936 loops=3)

  • Output: brs.branch_id, brs.invoice_id
  • Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
  • Rows Removed by Filter: 467064
  • Buffers: shared hit=7696
  • Worker 0: actual time=0.201..120.301 rows=936 loops=1
  • Buffers: shared hit=2570
  • Worker 1: actual time=0.495..118.092 rows=936 loops=1
  • Buffers: shared hit=2570
10.          

SubPlan (for Seq Scan)

11. 0.000 0.056 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.84..28.18 rows=1 width=8) (actual time=0.056..0.056 rows=0 loops=3)

  • Output: br1.id
  • Inner Unique: true
  • Join Filter: (ar1.group_id = grp1.id)
  • Buffers: shared hit=17
  • Worker 0: actual time=0.041..0.041 rows=0 loops=1
  • Buffers: shared hit=7
  • Worker 1: actual time=0.109..0.109 rows=0 loops=1
  • Buffers: shared hit=7
12. 0.000 0.056 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.84..27.13 rows=1 width=24) (actual time=0.056..0.056 rows=0 loops=3)

  • Output: br1.id, ar1.group_id, gpr1.group_id
  • Inner Unique: true
  • Join Filter: (gpr1.permission_id = prm1.id)
  • Buffers: shared hit=17
  • Worker 0: actual time=0.040..0.040 rows=0 loops=1
  • Buffers: shared hit=7
  • Worker 1: actual time=0.108..0.108 rows=0 loops=1
  • Buffers: shared hit=7
13. 0.001 0.056 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.84..26.06 rows=1 width=32) (actual time=0.056..0.056 rows=0 loops=3)

  • Output: br1.id, ar1.group_id, gpr1.group_id, gpr1.permission_id
  • Join Filter: (ar1.group_id = gpr1.group_id)
  • Buffers: shared hit=17
  • Worker 0: actual time=0.040..0.040 rows=0 loops=1
  • Buffers: shared hit=7
  • Worker 1: actual time=0.108..0.108 rows=0 loops=1
  • Buffers: shared hit=7
14. 0.000 0.055 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.84..24.93 rows=1 width=16) (actual time=0.055..0.055 rows=0 loops=3)

  • Output: br1.id, ar1.group_id
  • Buffers: shared hit=17
  • Worker 0: actual time=0.040..0.040 rows=0 loops=1
  • Buffers: shared hit=7
  • Worker 1: actual time=0.107..0.107 rows=0 loops=1
  • Buffers: shared hit=7
15. 0.001 0.055 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.56..16.61 rows=1 width=24) (actual time=0.055..0.055 rows=0 loops=3)

  • Output: br1.id, ar1.user_id, ar1.group_id
  • Inner Unique: true
  • Buffers: shared hit=17
  • Worker 0: actual time=0.040..0.040 rows=0 loops=1
  • Buffers: shared hit=7
  • Worker 1: actual time=0.107..0.107 rows=0 loops=1
  • Buffers: shared hit=7
16. 0.047 0.047 ↑ 1.0 1 3 / 3

Index Scan using "indx-27" on "slow-query".access_rights ar1 (cost=0.28..8.30 rows=1 width=24) (actual time=0.046..0.047 rows=1 loops=3)

  • Output: ar1.branch_id, ar1.user_id, ar1.group_id
  • Index Cond: (ar1.user_id = 1636)
  • Filter: ((ar1.access_type)::text = 'T1'::text)
  • Buffers: shared hit=17
  • Worker 0: actual time=0.034..0.034 rows=1 loops=1
  • Buffers: shared hit=7
  • Worker 1: actual time=0.094..0.095 rows=1 loops=1
  • Buffers: shared hit=7
17. 0.007 0.007 ↓ 0.0 0 3 / 3

Index Only Scan using "indx-3" on "slow-query".branches br1 (cost=0.28..4.29 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=3)

  • Output: br1.id
  • Index Cond: (br1.id = ar1.branch_id)
  • Heap Fetches: 0
  • Worker 0: actual time=0.005..0.005 rows=0 loops=1
  • Worker 1: actual time=0.010..0.010 rows=0 loops=1
18. 0.000 0.000 ↓ 0.0 0 / 3

Index Only Scan using "indx-17" on "slow-query".users usr1 (cost=0.29..8.30 rows=1 width=8) (never executed)

  • Output: usr1.id
  • Index Cond: (usr1.id = 1636)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on "slow-query".group_permissions gpr1 (cost=0.00..1.06 rows=6 width=16) (never executed)

  • Output: gpr1.group_id, gpr1.permission_id
20. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on "slow-query".permissions prm1 (cost=0.00..1.06 rows=1 width=8) (never executed)

  • Output: prm1.id
  • Filter: ((prm1.code)::text = 'C2'::text)
21. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on "slow-query".groups grp1 (cost=0.00..1.02 rows=2 width=8) (never executed)

  • Output: grp1.id
22. 0.040 0.143 ↓ 2.0 2 3 / 3

Sort (cost=25.04..25.05 rows=1 width=8) (actual time=0.143..0.143 rows=2 loops=3)

  • Output: br3.id
  • Sort Key: br3.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=89
  • Worker 0: actual time=0.110..0.110 rows=2 loops=1
  • Buffers: shared hit=33
  • Worker 1: actual time=0.258..0.258 rows=2 loops=1
  • Buffers: shared hit=33
23. 0.001 0.103 ↓ 2.0 2 3 / 3

Nested Loop (cost=1.11..25.03 rows=1 width=8) (actual time=0.088..0.103 rows=2 loops=3)

  • Output: br3.id
  • Inner Unique: true
  • Join Filter: (ar2.group_id = g2.id)
  • Buffers: shared hit=75
  • Worker 0: actual time=0.078..0.089 rows=2 loops=1
  • Buffers: shared hit=26
  • Worker 1: actual time=0.146..0.167 rows=2 loops=1
  • Buffers: shared hit=26
24. 0.001 0.094 ↓ 2.0 2 3 / 3

Nested Loop (cost=1.11..23.99 rows=1 width=24) (actual time=0.080..0.094 rows=2 loops=3)

  • Output: br3.id, ar2.group_id, gpr2.group_id
  • Buffers: shared hit=69
  • Worker 0: actual time=0.070..0.081 rows=2 loops=1
  • Buffers: shared hit=24
  • Worker 1: actual time=0.133..0.152 rows=2 loops=1
  • Buffers: shared hit=24
25. 0.002 0.065 ↓ 2.0 2 3 / 3

Nested Loop (cost=0.83..15.68 rows=1 width=32) (actual time=0.056..0.065 rows=2 loops=3)

  • Output: br3.id, ar2.user_id, ar2.group_id, gpr2.group_id
  • Buffers: shared hit=49
  • Worker 0: actual time=0.048..0.055 rows=2 loops=1
  • Buffers: shared hit=17
  • Worker 1: actual time=0.091..0.104 rows=2 loops=1
  • Buffers: shared hit=17
26. 0.005 0.053 ↑ 1.0 1 3 / 3

Nested Loop (cost=0.55..14.82 rows=1 width=40) (actual time=0.045..0.053 rows=1 loops=3)

  • Output: cmp.id, ar2.company_id, ar2.user_id, ar2.group_id, gpr2.group_id
  • Inner Unique: true
  • Join Filter: (gpr2.permission_id = prm2.id)
  • Rows Removed by Join Filter: 4
  • Buffers: shared hit=38
  • Worker 0: actual time=0.039..0.045 rows=1 loops=1
  • Buffers: shared hit=13
  • Worker 1: actual time=0.075..0.086 rows=1 loops=1
  • Buffers: shared hit=13
27. 0.003 0.038 ↓ 5.0 5 3 / 3

Nested Loop (cost=0.55..13.74 rows=1 width=48) (actual time=0.034..0.038 rows=5 loops=3)

  • Output: cmp.id, ar2.company_id, ar2.user_id, ar2.group_id, gpr2.group_id, gpr2.permission_id
  • Join Filter: (ar2.group_id = gpr2.group_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=23
  • Worker 0: actual time=0.029..0.031 rows=5 loops=1
  • Buffers: shared hit=8
  • Worker 1: actual time=0.058..0.063 rows=5 loops=1
  • Buffers: shared hit=8
28. 0.002 0.030 ↑ 1.0 1 3 / 3

Nested Loop (cost=0.55..12.61 rows=1 width=32) (actual time=0.029..0.030 rows=1 loops=3)

  • Output: cmp.id, ar2.company_id, ar2.user_id, ar2.group_id
  • Inner Unique: true
  • Buffers: shared hit=20
  • Worker 0: actual time=0.023..0.024 rows=1 loops=1
  • Buffers: shared hit=7
  • Worker 1: actual time=0.049..0.051 rows=1 loops=1
  • Buffers: shared hit=7
29. 0.006 0.006 ↑ 1.0 1 3 / 3

Index Scan using "indx-27" on "slow-query".access_rights ar2 (cost=0.28..8.30 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=3)

  • Output: ar2.company_id, ar2.user_id, ar2.group_id
  • Index Cond: (ar2.user_id = 1636)
  • Filter: ((ar2.access_type)::text = 'T1'::text)
  • Buffers: shared hit=9
  • Worker 0: actual time=0.002..0.003 rows=1 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.008..0.009 rows=1 loops=1
  • Buffers: shared hit=3
30. 0.022 0.022 ↑ 1.0 1 3 / 3

Index Only Scan using "indx-5" on "slow-query".companies cmp (cost=0.27..4.29 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=3)

  • Output: cmp.id
  • Index Cond: (cmp.id = ar2.company_id)
  • Heap Fetches: 0
  • Buffers: shared hit=11
  • Worker 0: actual time=0.020..0.020 rows=1 loops=1
  • Buffers: shared hit=4
  • Worker 1: actual time=0.038..0.038 rows=1 loops=1
  • Buffers: shared hit=4
31. 0.005 0.005 ↑ 1.0 6 3 / 3

Seq Scan on "slow-query".group_permissions gpr2 (cost=0.00..1.06 rows=6 width=16) (actual time=0.004..0.005 rows=6 loops=3)

  • Output: gpr2.group_id, gpr2.permission_id
  • Buffers: shared hit=3
  • Worker 0: actual time=0.004..0.005 rows=6 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.007..0.008 rows=6 loops=1
  • Buffers: shared hit=1
32. 0.010 0.010 ↑ 1.0 1 15 / 3

Seq Scan on "slow-query".permissions prm2 (cost=0.00..1.06 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=15)

  • Output: prm2.id
  • Filter: ((prm2.code)::text = 'C2'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=15
  • Worker 0: actual time=0.002..0.002 rows=1 loops=5
  • Buffers: shared hit=5
  • Worker 1: actual time=0.003..0.004 rows=1 loops=5
  • Buffers: shared hit=5
33. 0.010 0.010 ↑ 1.0 2 3 / 3

Index Scan using "indx-29" on "slow-query".branches br3 (cost=0.28..0.84 rows=2 width=16) (actual time=0.009..0.010 rows=2 loops=3)

  • Output: br3.id, br3.company_id
  • Index Cond: (br3.company_id = cmp.id)
  • Buffers: shared hit=11
  • Worker 0: actual time=0.008..0.009 rows=2 loops=1
  • Buffers: shared hit=4
  • Worker 1: actual time=0.014..0.015 rows=2 loops=1
  • Buffers: shared hit=4
34. 0.028 0.028 ↑ 1.0 1 6 / 3

Index Only Scan using "indx-17" on "slow-query".users usr2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=6)

  • Output: usr2.id
  • Index Cond: (usr2.id = 1636)
  • Heap Fetches: 2
  • Buffers: shared hit=20
  • Worker 0: actual time=0.012..0.012 rows=1 loops=2
  • Buffers: shared hit=7
  • Worker 1: actual time=0.022..0.023 rows=1 loops=2
  • Buffers: shared hit=7
35. 0.008 0.008 ↑ 2.0 1 6 / 3

Seq Scan on "slow-query".groups g2 (cost=0.00..1.02 rows=2 width=8) (actual time=0.004..0.004 rows=1 loops=6)

  • Output: g2.id
  • Buffers: shared hit=6
  • Worker 0: actual time=0.004..0.004 rows=1 loops=2
  • Buffers: shared hit=2
  • Worker 1: actual time=0.006..0.006 rows=1 loops=2
  • Buffers: shared hit=2
36. 62.394 88.192 ↑ 1.0 234,000 3 / 3

Hash (cost=3,605.00..3,605.00 rows=234,000 width=16) (actual time=88.192..88.192 rows=234,000 loops=3)

  • Output: cinvs.company_id, cinvs.invoice_id
  • Buckets: 131072 Batches: 4 Memory Usage: 3777kB
  • Buffers: shared hit=3795, temp written=2307
  • Worker 0: actual time=102.872..102.872 rows=234000 loops=1
  • Buffers: shared hit=1265, temp written=769
  • Worker 1: actual time=107.657..107.657 rows=234000 loops=1
  • Buffers: shared hit=1265, temp written=769
37. 25.798 25.798 ↑ 1.0 234,000 3 / 3

Seq Scan on "slow-query".company_invoices cinvs (cost=0.00..3,605.00 rows=234,000 width=16) (actual time=0.014..25.798 rows=234,000 loops=3)

  • Output: cinvs.company_id, cinvs.invoice_id
  • Buffers: shared hit=3795
  • Worker 0: actual time=0.019..26.015 rows=234000 loops=1
  • Buffers: shared hit=1265
  • Worker 1: actual time=0.016..36.419 rows=234000 loops=1
  • Buffers: shared hit=1265
38. 0.175 0.345 ↑ 1.0 1,000 3 / 3

Hash (cost=64.00..64.00 rows=1,000 width=18) (actual time=0.345..0.345 rows=1,000 loops=3)

  • Output: br.name, br.id
  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
  • Buffers: shared hit=162
  • Worker 0: actual time=0.330..0.330 rows=1000 loops=1
  • Buffers: shared hit=54
  • Worker 1: actual time=0.398..0.398 rows=1000 loops=1
  • Buffers: shared hit=54
39. 0.170 0.170 ↑ 1.0 1,000 3 / 3

Seq Scan on "slow-query".branches br (cost=0.00..64.00 rows=1,000 width=18) (actual time=0.010..0.170 rows=1,000 loops=3)

  • Output: br.name, br.id
  • Buffers: shared hit=162
  • Worker 0: actual time=0.012..0.171 rows=1000 loops=1
  • Buffers: shared hit=54
  • Worker 1: actual time=0.013..0.190 rows=1000 loops=1
  • Buffers: shared hit=54
Planning time : 3.362 ms
Execution time : 364.185 ms