explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xVGl

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 5,381.782 ↑ 1.0 15 1

Limit (cost=405,972.40..406,540.79 rows=15 width=174) (actual time=5,381.273..5,381.782 rows=15 loops=1)

2. 0.038 5,381.761 ↑ 35,173.3 15 1

Result (cost=405,972.40..20,398,113.92 rows=527,599 width=174) (actual time=5,381.272..5,381.761 rows=15 loops=1)

3. 25.380 5,381.018 ↑ 35,173.3 15 1

Sort (cost=405,972.40..407,291.40 rows=527,599 width=138) (actual time=5,381.016..5,381.018 rows=15 loops=1)

  • Sort Key: p.name
  • Sort Method: top-N heapsort Memory: 29kB
4. 454.677 5,355.638 ↑ 8.3 63,757 1

HashAggregate (cost=387,752.06..393,028.05 rows=527,599 width=138) (actual time=5,303.907..5,355.638 rows=63,757 loops=1)

  • Group Key: p.id, u.id, s.id, c2p.service, currency.key
5. 343.703 4,900.961 ↓ 1.4 760,171 1

Hash Left Join (cost=156,815.47..378,519.07 rows=527,599 width=138) (actual time=864.875..4,900.961 rows=760,171 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: 3789
6. 307.162 4,526.805 ↓ 1.4 763,960 1

Hash Join (cost=92,410.71..312,711.98 rows=530,962 width=159) (actual time=832.912..4,526.805 rows=763,960 loops=1)

  • Hash Cond: (i.id = ins.id)
7. 284.401 3,882.674 ↓ 1.4 763,960 1

Hash Left Join (cost=70,446.73..289,354.22 rows=530,961 width=163) (actual time=489.211..3,882.674 rows=763,960 loops=1)

  • Hash Cond: (i.id_currency = currency.id)
8. 280.871 3,598.255 ↓ 1.4 763,960 1

Hash Left Join (cost=70,444.81..287,818.07 rows=530,961 width=163) (actual time=489.180..3,598.255 rows=763,960 loops=1)

  • Hash Cond: (p.id_supplier = s.id)
9. 265.964 3,287.950 ↓ 1.4 763,960 1

Hash Left Join (cost=67,567.79..283,547.20 rows=530,961 width=130) (actual time=459.062..3,287.950 rows=763,960 loops=1)

  • Hash Cond: (p.id_unit = u.id)
10. 255.160 3,019.760 ↓ 1.4 763,960 1

Hash Left Join (cost=67,358.29..281,943.48 rows=530,961 width=120) (actual time=456.795..3,019.760 rows=763,960 loops=1)

  • Hash Cond: (p.id = c2p.id_product)
11. 243.048 2,762.722 ↑ 1.2 450,663 1

Hash Join (cost=67,157.00..272,415.35 rows=530,961 width=115) (actual time=454.862..2,762.722 rows=450,663 loops=1)

  • Hash Cond: (li.id_product = p.id)
12. 1,388.656 2,163.288 ↑ 1.2 450,663 1

Hash Join (cost=37,717.78..241,582.34 rows=530,961 width=33) (actual time=92.561..2,163.288 rows=450,663 loops=1)

  • Hash Cond: (li.id_invoice_segment = i.id)
13. 696.531 696.531 ↓ 1.0 7,034,075 1

Seq Scan on line_items li (cost=0.00..185,426.99 rows=7,023,799 width=12) (actual time=0.008..696.531 rows=7,034,075 loops=1)

14. 11.763 78.101 ↑ 1.1 57,982 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3570kB
15. 61.196 66.338 ↑ 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=6.598..66.338 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
16. 5.142 5.142 ↑ 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=5.142..5.142 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[]))
17. 244.675 356.386 ↓ 1.0 795,052 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 97780kB
18. 111.711 111.711 ↓ 1.0 795,052 1

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

19. 0.758 1.878 ↑ 1.0 5,244 1

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

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

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

  • Filter: (id_company = 64982)
  • Rows Removed by Filter: 2335
21. 1.171 2.226 ↑ 1.0 7,400 1

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

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

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

23. 14.070 29.434 ↓ 1.0 71,427 1

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

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

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

25. 0.007 0.018 ↑ 1.0 41 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 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)

27. 177.917 336.969 ↓ 1.0 800,094 1

Hash (cost=11,965.10..11,965.10 rows=799,910 width=4) (actual time=336.969..336.969 rows=800,094 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 36321kB
28. 159.052 159.052 ↓ 1.0 800,094 1

Seq Scan on invoice_segments ins (cost=0.00..11,965.10 rows=799,910 width=4) (actual time=0.014..159.052 rows=800,094 loops=1)

29. 9.629 29.254 ↓ 3.2 62,094 1

Hash (cost=64,159.92..64,159.92 rows=19,587 width=5) (actual time=29.254..29.254 rows=62,094 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2756kB
30. 13.726 19.625 ↓ 3.2 62,094 1

Bitmap Heap Scan on permission_cache_invoice pic (cost=819.95..64,159.92 rows=19,587 width=5) (actual time=6.299..19.625 rows=62,094 loops=1)

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

Bitmap Index Scan on permission_cache_invoice_composite (cost=0.00..815.05 rows=19,587 width=0) (actual time=5.899..5.899 rows=64,181 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.199 1.199 ↑ 1.0 7,472 1

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

  • Filter: (id_warehouse IS NOT NULL)
35.          

SubPlan (forResult)

36. 0.030 0.450 ↑ 1.0 1 15

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

37. 0.030 0.360 ↑ 1.0 1 15

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

38. 0.090 0.270 ↑ 1.0 1 15

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

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

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

40. 0.015 0.135 ↑ 1.0 1 15

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

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

  • Index Cond: (id = (max(li.id)))
42. 0.060 0.060 ↑ 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.004..0.004 rows=1 loops=15)

  • Index Cond: (id = li2.id_invoice_segment)
43. 0.060 0.060 ↑ 1.0 1 15

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

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

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

45. 0.015 0.210 ↑ 1.0 1 15

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

46. 0.090 0.165 ↑ 1.0 1 15

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

  • Hash Cond: (tr2_1.id = li2_1.id_tax_rate)
47. 0.030 0.030 ↑ 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.002 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 : 31.220 ms
Execution time : 5,411.177 ms