explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 68mE

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 23,928.278 ↑ 1.0 15 1

Limit (cost=54,848.45..55,416.85 rows=15 width=182) (actual time=23,925.749..23,928.278 rows=15 loops=1)

2. 0.058 23,928.258 ↑ 11.7 15 1

Result (cost=54,848.45..61,479.84 rows=175 width=182) (actual time=23,925.747..23,928.258 rows=15 loops=1)

3. 19.065 23,925.215 ↑ 11.7 15 1

Sort (cost=54,848.45..54,848.89 rows=175 width=146) (actual time=23,925.210..23,925.215 rows=15 loops=1)

  • Sort Key: p.name
  • Sort Method: top-N heapsort Memory: 31kB
4. 159.362 23,906.150 ↓ 193.2 33,814 1

GroupAggregate (cost=54,838.47..54,844.16 rows=175 width=146) (actual time=23,699.361..23,906.150 rows=33,814 loops=1)

  • Group Key: p.id, u.id, s.id, c2p.service, currency.key
5. 381.267 23,746.788 ↓ 1,462.1 255,865 1

Sort (cost=54,838.47..54,838.91 rows=175 width=138) (actual time=23,699.290..23,746.788 rows=255,865 loops=1)

  • Sort Key: p.id, u.id, s.id, c2p.service, currency.key
  • Sort Method: quicksort Memory: 63744kB
6. 17,563.576 23,365.521 ↓ 1,462.1 255,865 1

Nested Loop Left Join (cost=10,269.69..54,831.95 rows=175 width=138) (actual time=358.057..23,365.521 rows=255,865 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. 321.087 5,034.350 ↓ 729.0 255,865 1

Hash Anti Join (cost=10,269.12..51,741.40 rows=351 width=153) (actual time=338.265..5,034.350 rows=255,865 loops=1)

  • Hash Cond: (c2p.id = p2w.id_company2product)
8. 403.670 4,707.128 ↓ 28.4 848,742 1

Hash Left Join (cost=10,059.55..51,342.84 rows=29,880 width=157) (actual time=332.018..4,707.128 rows=848,742 loops=1)

  • Hash Cond: (p.id_supplier = s.id)
9. 435.045 4,263.311 ↓ 28.4 848,742 1

Hash Left Join (cost=7,186.99..48,391.84 rows=29,880 width=124) (actual time=291.119..4,263.311 rows=848,742 loops=1)

  • Hash Cond: (p.id_unit = u.id)
10. 526.449 3,825.354 ↓ 28.4 848,742 1

Hash Left Join (cost=6,978.21..48,104.60 rows=29,880 width=114) (actual time=288.143..3,825.354 rows=848,742 loops=1)

  • Hash Cond: (p.id = c2p.id_product)
  • Filter: ((NOT c2p.service) OR (c2p.service IS NULL))
  • Rows Removed by Filter: 14
11. 353.204 3,294.924 ↓ 15.1 455,316 1

Nested Loop (cost=6,776.95..47,375.13 rows=30,070 width=109) (actual time=284.096..3,294.924 rows=455,316 loops=1)

12. 230.069 1,575.772 ↓ 15.1 455,316 1

Nested Loop (cost=6,776.52..33,828.61 rows=30,070 width=27) (actual time=284.036..1,575.772 rows=455,316 loops=1)

13. 31.427 603.923 ↓ 16.7 57,060 1

Hash Left Join (cost=6,776.09..20,815.06 rows=3,410 width=23) (actual time=283.991..603.923 rows=57,060 loops=1)

  • Hash Cond: (i.id_currency = currency.id)
14. 303.704 572.461 ↓ 16.7 57,060 1

Hash Join (cost=6,774.17..20,803.28 rows=3,410 width=23) (actual time=283.936..572.461 rows=57,060 loops=1)

  • Hash Cond: (ins.id_invoice = i.id)
15. 96.507 96.507 ↓ 1.0 798,532 1

Seq Scan on invoice_segments ins (cost=0.00..11,935.53 rows=797,553 width=8) (actual time=0.011..96.507 rows=798,532 loops=1)

16. 21.705 172.250 ↓ 16.5 57,060 1

Hash (cost=6,730.84..6,730.84 rows=3,466 width=19) (actual time=172.249..172.250 rows=57,060 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3354kB
17. 150.545 150.545 ↓ 16.5 57,060 1

Index Scan using invoices_index_id_client on invoices i (cost=0.42..6,730.84 rows=3,466 width=19) (actual time=0.308..150.545 rows=57,060 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: 839
18. 0.017 0.035 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=8) (actual time=0.035..0.035 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.018 0.018 ↑ 1.0 41 1

Seq Scan on currencies currency (cost=0.00..1.41 rows=41 width=8) (actual time=0.009..0.018 rows=41 loops=1)

20. 741.780 741.780 ↑ 12.1 8 57,060

Index Scan using id_invoice_position_unique on line_items li (cost=0.43..2.85 rows=97 width=12) (actual time=0.004..0.013 rows=8 loops=57,060)

  • Index Cond: (id_invoice_segment = ins.id)
21. 1,365.948 1,365.948 ↑ 1.0 1 455,316

Index Scan using products_pkey on products p (cost=0.42..0.45 rows=1 width=86) (actual time=0.003..0.003 rows=1 loops=455,316)

  • Index Cond: (id = li.id_product)
22. 1.115 3.981 ↑ 1.0 5,244 1

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

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

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

  • Filter: (id_company = 64982)
  • Rows Removed by Filter: 2335
24. 1.515 2.912 ↓ 1.0 7,398 1

Hash (cost=116.68..116.68 rows=7,368 width=10) (actual time=2.912..2.912 rows=7,398 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 379kB
25. 1.397 1.397 ↓ 1.0 7,398 1

Seq Scan on units u (cost=0.00..116.68 rows=7,368 width=10) (actual time=0.010..1.397 rows=7,398 loops=1)

26. 18.630 40.147 ↓ 1.0 71,423 1

Hash (cost=1,982.25..1,982.25 rows=71,225 width=33) (actual time=40.147..40.147 rows=71,423 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5682kB
27. 21.517 21.517 ↓ 1.0 71,423 1

Seq Scan on companies s (cost=0.00..1,982.25 rows=71,225 width=33) (actual time=0.006..21.517 rows=71,423 loops=1)

28. 1.991 6.135 ↑ 1.0 7,472 1

Hash (cost=115.92..115.92 rows=7,492 width=4) (actual time=6.135..6.135 rows=7,472 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 327kB
29. 4.144 4.144 ↑ 1.0 7,472 1

Seq Scan on products2warehouses p2w (cost=0.00..115.92 rows=7,492 width=4) (actual time=0.010..4.144 rows=7,472 loops=1)

  • Filter: (id_warehouse IS NOT NULL)
30. 767.595 767.595 ↓ 0.0 0 255,865

Index Scan using permission_cache_invoice_composite on permission_cache_invoice pic (cost=0.56..8.54 rows=1 width=5) (actual time=0.003..0.003 rows=0 loops=255,865)

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

SubPlan (forResult)

32. 0.045 2.610 ↑ 1.0 1 15

Nested Loop (cost=9.31..18.95 rows=1 width=32) (actual time=0.169..0.174 rows=1 loops=15)

33. 0.045 2.460 ↑ 1.0 1 15

Nested Loop (cost=8.89..18.31 rows=1 width=14) (actual time=0.159..0.164 rows=1 loops=15)

34. 0.105 2.295 ↑ 1.0 1 15

Hash Join (cost=8.46..9.86 rows=1 width=14) (actual time=0.147..0.153 rows=1 loops=15)

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

Seq Scan on tax_rates tr2 (cost=0.00..1.31 rows=31 width=8) (actual time=0.002..0.004 rows=31 loops=15)

36. 0.030 2.130 ↑ 1.0 1 15

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 2.100 2.100 ↑ 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.140..0.140 rows=1 loops=15)

  • Index Cond: (id = (max(li.id)))
38. 0.120 0.120 ↑ 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.008..0.008 rows=1 loops=15)

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

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

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

Nested Loop (cost=9.31..18.94 rows=1 width=8) (actual time=0.019..0.025 rows=1 loops=15)

41. 0.060 0.330 ↑ 1.0 1 15

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

42. 0.135 0.240 ↑ 1.0 1 15

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

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

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

44. 0.000 0.060 ↑ 1.0 1 15

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.060 0.060 ↑ 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.003..0.004 rows=1 loops=15)

  • Index Cond: (id = (max(li.id)))
46. 0.030 0.030 ↑ 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.002..0.002 rows=1 loops=15)

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

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

  • Index Cond: (id = ins2_1.id_invoice)
Planning time : 51.666 ms
Execution time : 23,936.897 ms