explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kfl

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 6,670.929 ↑ 1.0 15 1

Limit (cost=410,646.62..411,215.01 rows=15 width=174) (actual time=6,669.813..6,670.929 rows=15 loops=1)

2. 0.029 6,670.908 ↑ 35,173.2 15 1

Result (cost=410,646.62..20,402,750.24 rows=527,598 width=174) (actual time=6,669.811..6,670.908 rows=15 loops=1)

3. 15.973 6,667.279 ↑ 35,173.2 15 1

Sort (cost=410,646.62..411,965.61 rows=527,598 width=138) (actual time=6,667.277..6,667.279 rows=15 loops=1)

  • Sort Key: p.name
  • Sort Method: top-N heapsort Memory: 31kB
4. 444.330 6,651.306 ↑ 14.2 37,125 1

HashAggregate (cost=392,426.31..397,702.29 rows=527,598 width=138) (actual time=6,621.573..6,651.306 rows=37,125 loops=1)

  • Group Key: p.id, u.id, s.id, c2p.service, currency.key
5. 422.061 6,206.976 ↓ 1.6 850,103 1

Hash Left Join (cost=149,672.23..383,193.34 rows=527,598 width=138) (actual time=909.060..6,206.976 rows=850,103 loops=1)

  • Hash Cond: (i.id = pic.id_invoice)
  • Filter: ((NOT c2p.service) OR ((c2p.service IS NULL) AND (NOT (alternatives: SubPlan 3 or hashed SubPlan 4)) AND i.online AND (NOT i.deleted) AND (NOT i.canceled) AND 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))
  • Rows Removed by Filter: 2551
6. 294.656 5,688.340 ↓ 1.6 852,654 1

Hash Left Join (cost=85,267.47..317,386.25 rows=530,961 width=159) (actual time=810.170..5,688.340 rows=852,654 loops=1)

  • Hash Cond: (i.id_currency = currency.id)
7. 290.693 5,393.664 ↓ 1.6 852,654 1

Hash Left Join (cost=85,265.55..315,850.10 rows=530,961 width=159) (actual time=810.132..5,393.664 rows=852,654 loops=1)

  • Hash Cond: (p.id_supplier = s.id)
8. 279.672 5,073.838 ↓ 1.6 852,654 1

Hash Left Join (cost=82,388.53..311,579.23 rows=530,961 width=126) (actual time=780.431..5,073.838 rows=852,654 loops=1)

  • Hash Cond: (p.id_unit = u.id)
9. 270.784 4,792.128 ↓ 1.6 852,654 1

Hash Left Join (cost=82,179.03..309,975.52 rows=530,961 width=116) (actual time=778.351..4,792.128 rows=852,654 loops=1)

  • Hash Cond: (p.id = c2p.id_product)
10. 233.585 4,519.329 ↑ 1.2 458,602 1

Hash Join (cost=81,977.74..300,447.38 rows=530,961 width=111) (actual time=776.285..4,519.329 rows=458,602 loops=1)

  • Hash Cond: (li.id_product = p.id)
11. 1,183.562 3,892.173 ↑ 1.2 458,602 1

Hash Join (cost=52,538.51..269,614.37 rows=530,961 width=29) (actual time=376.028..3,892.173 rows=458,602 loops=1)

  • Hash Cond: (li.id_invoice_segment = ins.id)
12. 2,356.494 2,356.494 ↓ 1.0 7,034,052 1

Seq Scan on line_items li (cost=0.00..185,426.99 rows=7,023,799 width=12) (actual time=0.007..2,356.494 rows=7,034,052 loops=1)

13. 13.984 352.117 ↑ 1.0 57,918 1

Hash (cost=51,782.65..51,782.65 rows=60,469 width=25) (actual time=352.116..352.117 rows=57,918 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3793kB
14. 169.823 338.133 ↑ 1.0 57,918 1

Hash Join (cost=37,717.78..51,782.65 rows=60,469 width=25) (actual time=152.400..338.133 rows=57,918 loops=1)

  • Hash Cond: (ins.id_invoice = i.id)
15. 78.652 78.652 ↓ 1.0 800,089 1

Seq Scan on invoice_segments ins (cost=0.00..11,965.10 rows=799,910 width=8) (actual time=0.008..78.652 rows=800,089 loops=1)

16. 16.126 89.658 ↑ 1.1 57,982 1

Hash (cost=36,950.34..36,950.34 rows=61,395 width=21) (actual time=89.658..89.658 rows=57,982 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3570kB
17. 66.536 73.532 ↑ 1.1 57,982 1

Bitmap Heap Scan on invoices i (cost=2,158.19..36,950.34 rows=61,395 width=21) (actual time=9.061..73.532 rows=57,982 loops=1)

  • Recheck Cond: (id_client = ANY ('{64982,1411,64990,2381,64989,64983,64984,2391,64985,64986,64988,64987,64992,2716,64991}'::integer[]))
  • Heap Blocks: exact=11060
18. 6.996 6.996 ↑ 1.1 58,006 1

Bitmap Index Scan on invoices_index_id_client (cost=0.00..2,142.84 rows=61,395 width=0) (actual time=6.996..6.996 rows=58,006 loops=1)

  • Index Cond: (id_client = ANY ('{64982,1411,64990,2381,64989,64983,64984,2391,64985,64986,64988,64987,64992,2716,64991}'::integer[]))
19. 266.043 393.571 ↓ 1.0 795,047 1

Hash (cost=19,502.99..19,502.99 rows=794,899 width=86) (actual time=393.571..393.571 rows=795,047 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 97780kB
20. 127.528 127.528 ↓ 1.0 795,047 1

Seq Scan on products p (cost=0.00..19,502.99 rows=794,899 width=86) (actual time=0.020..127.528 rows=795,047 loops=1)

21. 0.813 2.015 ↑ 1.0 5,244 1

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

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

Seq Scan on companies2products c2p (cost=0.00..135.74 rows=5,244 width=9) (actual time=0.022..1.202 rows=5,244 loops=1)

  • Filter: (id_company = 64982)
  • Rows Removed by Filter: 2335
23. 1.087 2.038 ↑ 1.0 7,400 1

Hash (cost=117.00..117.00 rows=7,400 width=10) (actual time=2.038..2.038 rows=7,400 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 379kB
24. 0.951 0.951 ↑ 1.0 7,400 1

Seq Scan on units u (cost=0.00..117.00 rows=7,400 width=10) (actual time=0.007..0.951 rows=7,400 loops=1)

25. 13.982 29.133 ↓ 1.0 71,427 1

Hash (cost=1,984.23..1,984.23 rows=71,423 width=33) (actual time=29.133..29.133 rows=71,427 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5682kB
26. 15.151 15.151 ↓ 1.0 71,427 1

Seq Scan on companies s (cost=0.00..1,984.23 rows=71,423 width=33) (actual time=0.008..15.151 rows=71,427 loops=1)

27. 0.009 0.020 ↑ 1.0 41 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 0.011 0.011 ↑ 1.0 41 1

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

29. 17.694 94.609 ↓ 3.2 61,905 1

Hash (cost=64,159.92..64,159.92 rows=19,587 width=5) (actual time=94.609..94.609 rows=61,905 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2749kB
30. 47.013 76.915 ↓ 3.2 61,905 1

Bitmap Heap Scan on permission_cache_invoice pic (cost=819.95..64,159.92 rows=19,587 width=5) (actual time=33.079..76.915 rows=61,905 loops=1)

  • Recheck Cond: ((id_user = 3) AND (id_permission = 3) AND (direction = 'one_of_parents'::acl.direction))
  • Heap Blocks: exact=719
31. 29.902 29.902 ↓ 3.3 64,099 1

Bitmap Index Scan on permission_cache_invoice_composite (cost=0.00..815.05 rows=19,587 width=0) (actual time=29.902..29.902 rows=64,099 loops=1)

  • Index Cond: ((id_user = 3) AND (id_permission = 3) AND (direction = 'one_of_parents'::acl.direction))
32.          

SubPlan (forHash Left Join)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using products2warehouses_id_company2product on products2warehouses p2w (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: (id_company2product = c2p.id)
  • Filter: (id_warehouse IS NOT NULL)
34. 1.966 1.966 ↑ 1.0 7,472 1

Seq Scan on products2warehouses p2w_1 (cost=0.00..115.72 rows=7,472 width=4) (actual time=0.015..1.966 rows=7,472 loops=1)

  • Filter: (id_warehouse IS NOT NULL)
35.          

SubPlan (forResult)

36. 0.045 3.240 ↑ 1.0 1 15

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

37. 0.030 3.075 ↑ 1.0 1 15

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

38. 2.280 2.925 ↑ 1.0 1 15

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

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

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

40. 0.015 0.555 ↑ 1.0 1 15

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.540 0.540 ↑ 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.036..0.036 rows=1 loops=15)

  • Index Cond: (id = (max(li.id)))
42. 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)
43. 0.120 0.120 ↑ 1.0 1 15

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

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

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

45. 0.030 0.315 ↑ 1.0 1 15

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

46. 0.135 0.255 ↑ 1.0 1 15

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

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

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

48. 0.015 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
49. 0.030 0.030 ↑ 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.002 rows=1 loops=15)

  • Index Cond: (id = (max(li.id)))
50. 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)
51. 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 : 28.577 ms
Execution time : 6,697.724 ms