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. 0.000 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.435 1,013.616 ↑ 988.2 148 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.297 1,013.181 ↑ 468.8 312 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. 50.631 1,011.849 ↑ 468.8 312 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. 296.511 696.642 ↑ 468.8 312 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. 98.760 98.760 ↑ 2.0 262,077 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. 1.134 301.371 ↑ 375.0 936 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. 299.640 300.237 ↑ 375.0 936 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 (forSeq Scan)

11. 0.000 0.168 ↓ 0.0 0 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.168 ↓ 0.0 0 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.003 0.168 ↓ 0.0 0 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.165 ↓ 0.0 0 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.003 0.165 ↓ 0.0 0 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.141 0.141 ↑ 1.0 1 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.021 0.021 ↓ 0.0 0 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

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

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

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

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

  • Output: grp1.id
22. 0.120 0.429 ↓ 2.0 2 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.003 0.309 ↓ 2.0 2 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.003 0.282 ↓ 2.0 2 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.006 0.195 ↓ 2.0 2 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.015 0.159 ↑ 1.0 1 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.009 0.114 ↓ 5.0 5 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.006 0.090 ↑ 1.0 1 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.018 0.018 ↑ 1.0 1 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.066 0.066 ↑ 1.0 1 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.015 0.015 ↑ 1.0 6 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.030 0.030 ↑ 1.0 1 15

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.030 0.030 ↑ 1.0 2 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.084 0.084 ↑ 1.0 1 6

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.024 0.024 ↑ 2.0 1 6

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. 187.182 264.576 ↑ 1.0 234,000 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. 77.394 77.394 ↑ 1.0 234,000 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.525 1.035 ↑ 1.0 1,000 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.510 0.510 ↑ 1.0 1,000 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