explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Hqj

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 6,968.880 ↑ 1.0 15 1

Limit (cost=54,848.97..55,417.38 rows=15 width=182) (actual time=6,967.997..6,968.880 rows=15 loops=1)

2. 0.056 6,968.842 ↑ 11.7 15 1

Result (cost=54,848.97..61,480.36 rows=175 width=182) (actual time=6,967.996..6,968.842 rows=15 loops=1)

3. 15.292 6,967.631 ↑ 11.7 15 1

Sort (cost=54,848.97..54,849.41 rows=175 width=146) (actual time=6,967.628..6,967.631 rows=15 loops=1)

  • Sort Key: p.name
  • Sort Method: top-N heapsort Memory: 31kB
4. 139.680 6,952.339 ↓ 193.2 33,814 1

GroupAggregate (cost=54,838.99..54,844.68 rows=175 width=146) (actual time=6,768.693..6,952.339 rows=33,814 loops=1)

  • Group Key: p.id, u.id, s.id, c2p.service, currency.key
5. 372.803 6,812.659 ↓ 1,462.2 255,878 1

Sort (cost=54,838.99..54,839.43 rows=175 width=138) (actual time=6,768.607..6,812.659 rows=255,878 loops=1)

  • Sort Key: p.id, u.id, s.id, c2p.service, currency.key
  • Sort Method: quicksort Memory: 63748kB
6. 316.631 6,439.856 ↓ 1,462.2 255,878 1

Nested Loop Left Join (cost=10,269.69..54,832.47 rows=175 width=138) (actual time=308.438..6,439.856 rows=255,878 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. 394.543 5,355.591 ↓ 729.0 255,878 1

Hash Anti Join (cost=10,269.12..51,741.92 rows=351 width=153) (actual time=308.387..5,355.591 rows=255,878 loops=1)

  • Hash Cond: (c2p.id = p2w.id_company2product)
8. 537.497 4,958.252 ↓ 28.4 848,755 1

Hash Left Join (cost=10,059.55..51,343.36 rows=29,881 width=157) (actual time=305.499..4,958.252 rows=848,755 loops=1)

  • Hash Cond: (p.id_supplier = s.id)
9. 505.572 4,381.496 ↓ 28.4 848,755 1

Hash Left Join (cost=7,186.99..48,392.36 rows=29,881 width=124) (actual time=265.493..4,381.496 rows=848,755 loops=1)

  • Hash Cond: (p.id_unit = u.id)
10. 620.571 3,873.036 ↓ 28.4 848,755 1

Hash Left Join (cost=6,978.21..48,105.12 rows=29,881 width=114) (actual time=262.550..3,873.036 rows=848,755 loops=1)

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

Nested Loop (cost=6,776.95..47,375.62 rows=30,071 width=109) (actual time=259.789..3,249.765 rows=455,329 loops=1)

12. 234.270 1,244.401 ↓ 15.1 455,329 1

Nested Loop (cost=6,776.52..33,828.66 rows=30,071 width=27) (actual time=259.754..1,244.401 rows=455,329 loops=1)

13. 22.625 496.573 ↓ 16.7 57,062 1

Hash Left Join (cost=6,776.09..20,815.06 rows=3,410 width=23) (actual time=259.713..496.573 rows=57,062 loops=1)

  • Hash Cond: (i.id_currency = currency.id)
14. 231.961 473.917 ↓ 16.7 57,062 1

Hash Join (cost=6,774.17..20,803.28 rows=3,410 width=23) (actual time=259.668..473.917 rows=57,062 loops=1)

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

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

16. 16.654 153.128 ↓ 16.5 57,062 1

Hash (cost=6,730.84..6,730.84 rows=3,466 width=19) (actual time=153.128..153.128 rows=57,062 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3354kB
17. 136.474 136.474 ↓ 16.5 57,062 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.170..136.474 rows=57,062 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: 837
18. 0.015 0.031 ↑ 1.0 41 1

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

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

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

20. 513.558 513.558 ↑ 12.1 8 57,062

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.009 rows=8 loops=57,062)

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

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,329)

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

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

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

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

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

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

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

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

26. 18.556 39.259 ↓ 1.0 71,423 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5682kB
27. 20.703 20.703 ↓ 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..20.703 rows=71,423 loops=1)

28. 1.309 2.796 ↑ 1.0 7,472 1

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

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

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

  • Filter: (id_warehouse IS NOT NULL)
30. 767.634 767.634 ↑ 1.0 1 255,878

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=1 loops=255,878)

  • 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 0.855 ↑ 1.0 1 15

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

33. 0.030 0.675 ↑ 1.0 1 15

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

34. 0.285 0.525 ↑ 1.0 1 15

Hash Join (cost=8.46..9.86 rows=1 width=14) (actual time=0.030..0.035 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.015 0.180 ↑ 1.0 1 15

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.165 0.165 ↑ 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.011..0.011 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.135 0.135 ↑ 1.0 1 15

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

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

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

41. 0.045 0.255 ↑ 1.0 1 15

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

42. 0.075 0.165 ↑ 1.0 1 15

Hash Join (cost=8.46..9.86 rows=1 width=4) (actual time=0.007..0.011 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.045 ↑ 1.0 1 15

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

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

  • Index Cond: (id = (max(li.id)))
46. 0.045 0.045 ↑ 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.003..0.003 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 : 33.621 ms
Execution time : 6,976.642 ms