explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LedB

Settings
# exclusive inclusive rows x rows loops node
1. 0.908 89,171.862 ↑ 1.0 15 1

Limit (cost=58,930.47..59,497.01 rows=15 width=236) (actual time=89,156.792..89,171.862 rows=15 loops=1)

2. 0.498 89,170.954 ↑ 12.6 15 1

Result (cost=58,930.47..66,068.90 rows=189 width=236) (actual time=89,156.778..89,170.954 rows=15 loops=1)

3. 228.493 89,155.711 ↑ 12.6 15 1

Sort (cost=58,930.47..58,930.94 rows=189 width=200) (actual time=89,155.625..89,155.711 rows=15 loops=1)

  • Sort Key: p.name
  • Sort Method: top-N heapsort Memory: 31kB
4. 1,852.815 88,927.218 ↓ 177.9 33,617 1

GroupAggregate (cost=58,919.69..58,925.83 rows=189 width=200) (actual time=85,427.951..88,927.218 rows=33,617 loops=1)

  • Group Key: p.id, u.id, s.id, c2p.service, currency.key
5. 3,457.301 87,074.403 ↓ 1,340.8 253,407 1

Sort (cost=58,919.69..58,920.16 rows=189 width=192) (actual time=85,426.685..87,074.403 rows=253,407 loops=1)

  • Sort Key: p.id, u.id, s.id, c2p.service, currency.key
  • Sort Method: external merge Disk: 33504kB
6. 4,789.511 83,617.102 ↓ 1,340.8 253,407 1

Nested Loop Left Join (cost=10,194.15..58,912.54 rows=189 width=192) (actual time=6,629.716..83,617.102 rows=253,407 loops=1)

  • Filter: CASE WHEN pic.value THEN true WHEN (NOT pic.value) THEN false ELSE acl.has_permission_for_invoice(3, 3, i.id, i.id_client, i.id_supplier, i.online, i.accounted, i.accounted_partially, 'one_of_parents'::acl.direction) END
7. 6,878.331 76,546.928 ↓ 670.4 253,407 1

Hash Anti Join (cost=10,193.73..57,584.98 rows=378 width=207) (actual time=6,629.640..76,546.928 rows=253,407 loops=1)

  • Hash Cond: (c2p.id = p2w.id_company2product)
8. 10,400.635 69,558.268 ↓ 24.2 844,695 1

Hash Left Join (cost=9,984.00..57,154.72 rows=34,900 width=211) (actual time=6,519.259..69,558.268 rows=844,695 loops=1)

  • Hash Cond: (i.id_currency = currency.id)
9. 10,404.351 59,156.990 ↓ 24.2 844,695 1

Hash Left Join (cost=9,982.08..57,051.93 rows=34,900 width=157) (actual time=6,518.580..59,156.990 rows=844,695 loops=1)

  • Hash Cond: (p.id_unit = u.id)
10. 8,200.755 48,639.760 ↓ 24.2 844,695 1

Hash Left Join (cost=9,774.30..56,752.51 rows=34,900 width=147) (actual time=6,405.642..48,639.760 rows=844,695 loops=1)

  • Hash Cond: (p.id = c2p.id_product)
  • Filter: ((NOT c2p.service) OR (c2p.service IS NULL))
  • Rows Removed by Filter: 14
11. 5,839.433 40,365.855 ↓ 12.9 452,563 1

Hash Left Join (cost=9,573.03..55,934.28 rows=35,122 width=142) (actual time=6,332.434..40,365.855 rows=452,563 loops=1)

  • Hash Cond: (p.id_supplier = s.id)
12. 8,630.128 33,597.490 ↓ 12.9 452,563 1

Nested Loop (cost=6,387.80..50,932.85 rows=35,122 width=109) (actual time=4,888.882..33,597.490 rows=452,563 loops=1)

13. 6,143.187 20,894.295 ↓ 12.9 452,563 1

Nested Loop (cost=6,387.37..35,124.56 rows=35,122 width=27) (actual time=4,888.838..20,894.295 rows=452,563 loops=1)

14. 5,394.727 11,121.156 ↓ 14.2 56,718 1

Hash Join (cost=6,386.94..20,295.23 rows=3,997 width=23) (actual time=4,888.785..11,121.156 rows=56,718 loops=1)

  • Hash Cond: (ins.id_invoice = i.id)
15. 4,809.259 4,809.259 ↓ 1.0 791,312 1

Seq Scan on invoice_segments ins (cost=0.00..11,831.09 rows=791,309 width=8) (actual time=0.017..4,809.259 rows=791,312 loops=1)

16. 407.939 917.170 ↓ 14.0 56,718 1

Hash (cost=6,336.24..6,336.24 rows=4,056 width=19) (actual time=917.153..917.170 rows=56,718 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3337kB
17. 509.231 509.231 ↓ 14.0 56,718 1

Index Scan using invoices_index_id_client on invoices i (cost=0.42..6,336.24 rows=4,056 width=19) (actual time=0.096..509.231 rows=56,718 loops=1)

  • Index Cond: ((id_client = ANY ('{64982,1411,64990,2381,64989,64983,64984,2391,64985,64986,64988,64987,64992,2716,64991}'::integer[])) AND (id_client = ANY ('{64982,1411,64990,2381,64989,64983,64984,2391,64985,64986,64988,64987,64992,2716,64991}'::integer[])))
  • Filter: (online AND (NOT deleted) AND (NOT canceled))
  • Rows Removed by Filter: 749
18. 3,629.952 3,629.952 ↑ 11.9 8 56,718

Index Scan using id_invoice_position_unique on line_items li (cost=0.43..2.76 rows=95 width=12) (actual time=0.010..0.064 rows=8 loops=56,718)

  • Index Cond: (id_invoice_segment = ins.id)
19. 4,073.067 4,073.067 ↑ 1.0 1 452,563

Index Scan using products_pkey on products p (cost=0.42..0.45 rows=1 width=86) (actual time=0.009..0.009 rows=1 loops=452,563)

  • Index Cond: (id = li.id_product)
20. 468.945 928.932 ↑ 1.0 71,166 1

Hash (cost=1,739.66..1,739.66 rows=71,166 width=33) (actual time=928.922..928.932 rows=71,166 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2849kB
21. 459.987 459.987 ↑ 1.0 71,166 1

Seq Scan on companies s (cost=0.00..1,739.66 rows=71,166 width=33) (actual time=0.016..459.987 rows=71,166 loops=1)

22. 36.546 73.150 ↑ 1.0 5,244 1

Hash (cost=135.71..135.71 rows=5,244 width=9) (actual time=73.139..73.150 rows=5,244 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 290kB
23. 36.604 36.604 ↑ 1.0 5,244 1

Seq Scan on companies2products c2p (cost=0.00..135.71 rows=5,244 width=9) (actual time=0.018..36.604 rows=5,244 loops=1)

  • Filter: (id_company = 64982)
  • Rows Removed by Filter: 2333
24. 57.356 112.879 ↑ 1.0 7,368 1

Hash (cost=115.68..115.68 rows=7,368 width=10) (actual time=112.869..112.879 rows=7,368 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 378kB
25. 55.523 55.523 ↑ 1.0 7,368 1

Seq Scan on units u (cost=0.00..115.68 rows=7,368 width=10) (actual time=0.019..55.523 rows=7,368 loops=1)

26. 0.324 0.643 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=62) (actual time=0.633..0.643 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.319 0.319 ↑ 1.0 41 1

Seq Scan on currencies currency (cost=0.00..1.41 rows=41 width=62) (actual time=0.022..0.319 rows=41 loops=1)

28. 54.671 110.329 ↑ 1.0 7,499 1

Hash (cost=115.99..115.99 rows=7,499 width=4) (actual time=110.319..110.329 rows=7,499 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 328kB
29. 55.658 55.658 ↑ 1.0 7,499 1

Seq Scan on products2warehouses p2w (cost=0.00..115.99 rows=7,499 width=4) (actual time=0.023..55.658 rows=7,499 loops=1)

  • Filter: (id_warehouse IS NOT NULL)
30. 2,280.663 2,280.663 ↑ 1.0 1 253,407

Index Scan using permission_cache_invoice_index_invoice on permission_cache_invoice pic (cost=0.42..3.25 rows=1 width=5) (actual time=0.009..0.009 rows=1 loops=253,407)

  • Index Cond: (id_invoice = i.id)
  • Filter: ((id_user = 3) AND (id_permission = 3) AND (direction = 'one_of_parents'::acl.direction))
31.          

SubPlan (forResult)

32. 0.375 7.365 ↑ 1.0 1 15

Nested Loop (cost=9.31..18.88 rows=1 width=32) (actual time=0.153..0.491 rows=1 loops=15)

33. 0.420 6.720 ↑ 1.0 1 15

Nested Loop (cost=8.89..18.31 rows=1 width=24) (actual time=0.118..0.448 rows=1 loops=15)

34. 2.790 6.015 ↑ 1.0 1 15

Hash Join (cost=8.46..9.86 rows=1 width=24) (actual time=0.083..0.401 rows=1 loops=15)

  • Hash Cond: (tr2.id = li2.id_tax_rate)
35. 2.580 2.580 ↑ 1.0 31 15

Seq Scan on tax_rates tr2 (cost=0.00..1.31 rows=31 width=18) (actual time=0.009..0.172 rows=31 loops=15)

36. 0.300 0.645 ↑ 1.0 1 15

Hash (cost=8.45..8.45 rows=1 width=14) (actual time=0.039..0.043 rows=1 loops=15)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.345 0.345 ↑ 1.0 1 15

Index Scan using line_items_pkey on line_items li2 (cost=0.43..8.45 rows=1 width=14) (actual time=0.017..0.023 rows=1 loops=15)

  • Index Cond: (id = (max(li.id)))
38. 0.285 0.285 ↑ 1.0 1 15

Index Scan using invoice_segments_pkey on invoice_segments ins2 (cost=0.42..8.44 rows=1 width=8) (actual time=0.017..0.019 rows=1 loops=15)

  • Index Cond: (id = li2.id_invoice_segment)
39. 0.270 0.270 ↑ 1.0 1 15

Index Scan using invoices_pkey on invoices i2 (cost=0.42..0.57 rows=1 width=5) (actual time=0.016..0.018 rows=1 loops=15)

  • Index Cond: (id = ins2.id_invoice)
40. 0.450 7.380 ↑ 1.0 1 15

Nested Loop (cost=9.31..18.87 rows=1 width=8) (actual time=0.145..0.492 rows=1 loops=15)

41. 0.405 6.780 ↑ 1.0 1 15

Nested Loop (cost=8.89..18.31 rows=1 width=4) (actual time=0.118..0.452 rows=1 loops=15)

42. 2.910 6.105 ↑ 1.0 1 15

Hash Join (cost=8.46..9.86 rows=1 width=4) (actual time=0.084..0.407 rows=1 loops=15)

  • Hash Cond: (tr2_1.id = li2_1.id_tax_rate)
43. 2.685 2.685 ↑ 1.0 31 15

Seq Scan on tax_rates tr2_1 (cost=0.00..1.31 rows=31 width=4) (actual time=0.006..0.179 rows=31 loops=15)

44. 0.270 0.510 ↑ 1.0 1 15

Hash (cost=8.45..8.45 rows=1 width=8) (actual time=0.034..0.034 rows=1 loops=15)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.240 0.240 ↑ 1.0 1 15

Index Scan using line_items_pkey on line_items li2_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.010..0.016 rows=1 loops=15)

  • Index Cond: (id = (max(li.id)))
46. 0.270 0.270 ↑ 1.0 1 15

Index Scan using invoice_segments_pkey on invoice_segments ins2_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=15)

  • Index Cond: (id = li2_1.id_invoice_segment)
47. 0.150 0.150 ↑ 1.0 1 15

Index Scan using invoices_pkey on invoices i2_1 (cost=0.42..0.57 rows=1 width=12) (actual time=0.009..0.010 rows=1 loops=15)

  • Index Cond: (id = ins2_1.id_invoice)
Planning time : 20.768 ms
Execution time : 89,181.214 ms