explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KZ8j

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 5,393.198 ↑ 1.0 15 1

Limit (cost=406,179.57..406,747.96 rows=15 width=174) (actual time=5,392.539..5,393.198 rows=15 loops=1)

2. 0.038 5,393.182 ↑ 35,226.1 15 1

Result (cost=406,179.57..20,428,320.60 rows=528,392 width=174) (actual time=5,392.538..5,393.182 rows=15 loops=1)

3. 35.586 5,392.334 ↑ 35,226.1 15 1

Sort (cost=406,179.57..407,500.55 rows=528,392 width=138) (actual time=5,392.332..5,392.334 rows=15 loops=1)

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

HashAggregate (cost=387,931.84..393,215.76 rows=528,392 width=138) (actual time=5,294.452..5,356.748 rows=63,757 loops=1)

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

Hash Left Join (cost=156,821.01..378,684.98 rows=528,392 width=138) (actual time=1,007.574..4,909.483 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. 284.134 4,411.486 ↓ 1.4 763,960 1

Hash Left Join (cost=92,416.25..312,875.79 rows=531,760 width=159) (actual time=833.722..4,411.486 rows=763,960 loops=1)

  • Hash Cond: (i.id_currency = currency.id)
7. 269.514 4,127.334 ↓ 1.4 763,960 1

Hash Left Join (cost=92,414.33..311,337.33 rows=531,760 width=159) (actual time=833.689..4,127.334 rows=763,960 loops=1)

  • Hash Cond: (p.id_supplier = s.id)
8. 255.051 3,826.900 ↓ 1.4 763,960 1

Hash Left Join (cost=89,537.31..307,064.36 rows=531,760 width=126) (actual time=802.199..3,826.900 rows=763,960 loops=1)

  • Hash Cond: (p.id_unit = u.id)
9. 239.993 3,569.775 ↓ 1.4 763,960 1

Hash Left Join (cost=89,327.81..305,458.55 rows=531,760 width=116) (actual time=800.085..3,569.775 rows=763,960 loops=1)

  • Hash Cond: (p.id = c2p.id_product)
10. 229.833 3,327.797 ↑ 1.2 450,663 1

Hash Join (cost=89,126.52..295,916.38 rows=531,760 width=111) (actual time=798.050..3,327.797 rows=450,663 loops=1)

  • Hash Cond: (li.id_product = p.id)
11. 141.889 2,714.931 ↑ 1.2 450,663 1

Hash Join (cost=59,687.30..265,081.27 rows=531,760 width=29) (actual time=408.443..2,714.931 rows=450,663 loops=1)

  • Hash Cond: (i.id = ins.id)
12. 1,458.032 2,272.810 ↑ 1.2 450,663 1

Hash Join (cost=37,717.78..241,715.87 rows=531,760 width=33) (actual time=102.569..2,272.810 rows=450,663 loops=1)

  • Hash Cond: (li.id_invoice_segment = i.id)
13. 727.624 727.624 ↑ 1.0 7,034,260 1

Seq Scan on line_items li (cost=0.00..185,532.76 rows=7,034,376 width=12) (actual time=0.007..727.624 rows=7,034,260 loops=1)

14. 11.432 87.154 ↑ 1.1 57,982 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3570kB
15. 70.255 75.722 ↑ 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.986..75.722 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=11054
16. 5.467 5.467 ↑ 1.1 57,998 1

Bitmap Index Scan on invoices_index_id_client (cost=0.00..2,142.84 rows=61,395 width=0) (actual time=5.467..5.467 rows=57,998 loops=1)

  • Index Cond: (id_client = ANY ('{64982,1411,64990,2381,64989,64983,64984,2391,64985,64986,64988,64987,64992,2716,64991}'::integer[]))
17. 154.166 300.232 ↓ 1.0 800,127 1

Hash (cost=11,968.12..11,968.12 rows=800,112 width=4) (actual time=300.231..300.232 rows=800,127 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 36322kB
18. 146.066 146.066 ↓ 1.0 800,127 1

Seq Scan on invoice_segments ins (cost=0.00..11,968.12 rows=800,112 width=4) (actual time=0.017..146.066 rows=800,127 loops=1)

19. 261.223 383.033 ↓ 1.0 795,067 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 97783kB
20. 121.810 121.810 ↓ 1.0 795,067 1

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

21. 0.777 1.985 ↑ 1.0 5,244 1

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

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

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

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 379kB
24. 0.990 0.990 ↑ 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.990 rows=7,400 loops=1)

25. 13.786 30.920 ↓ 1.0 71,427 1

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

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

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

27. 0.008 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
28. 0.010 0.010 ↑ 1.0 41 1

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

29. 9.808 171.124 ↓ 3.2 62,093 1

Hash (cost=64,159.92..64,159.92 rows=19,587 width=5) (actual time=171.123..171.124 rows=62,093 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2756kB
30. 87.025 161.316 ↓ 3.2 62,093 1

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

  • Recheck Cond: ((id_user = 3) AND (id_permission = 3) AND (direction = 'one_of_parents'::acl.direction))
  • Heap Blocks: exact=8037
31. 74.291 74.291 ↓ 31.4 614,763 1

Bitmap Index Scan on permission_cache_invoice_composite (cost=0.00..815.05 rows=19,587 width=0) (actual time=74.291..74.291 rows=614,763 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.241 1.241 ↑ 1.0 7,472 1

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

  • Filter: (id_warehouse IS NOT NULL)
35.          

SubPlan (forResult)

36. 0.030 0.495 ↑ 1.0 1 15

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

37. 0.045 0.390 ↑ 1.0 1 15

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

38. 0.120 0.270 ↑ 1.0 1 15

Hash Join (cost=8.46..9.86 rows=1 width=14) (actual time=0.015..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.001..0.003 rows=31 loops=15)

40. 0.015 0.105 ↑ 1.0 1 15

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

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

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

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

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

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

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

45. 0.045 0.270 ↑ 1.0 1 15

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

46. 0.090 0.195 ↑ 1.0 1 15

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

  • Hash Cond: (tr2_1.id = li2_1.id_tax_rate)
47. 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)

48. 0.015 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
49. 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.003..0.003 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 : 30.431 ms
Execution time : 5,420.902 ms