explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ynP8

Settings
# exclusive inclusive rows x rows loops node
1. 5.298 8,139.926 ↓ 33.3 3,265 1

Sort (cost=38,273.00..38,273.24 rows=98 width=59) (actual time=8,139.771..8,139.926 rows=3,265 loops=1)

  • Sort Key: p.name
  • Sort Method: quicksort Memory: 517kB
2. 118.990 8,134.628 ↓ 33.3 3,265 1

GroupAggregate (cost=38,267.06..38,269.76 rows=98 width=59) (actual time=7,952.434..8,134.628 rows=3,265 loops=1)

  • Group Key: p.id, u.id, s.id, c2p.service, currency.key
3. 359.178 8,015.638 ↓ 6,050.0 592,896 1

Sort (cost=38,267.06..38,267.31 rows=98 width=51) (actual time=7,952.422..8,015.638 rows=592,896 loops=1)

  • Sort Key: p.id, u.id, s.id, c2p.service, currency.key
  • Sort Method: quicksort Memory: 79941kB
4. 12.336 7,656.460 ↓ 6,050.0 592,896 1

Nested Loop (cost=6,979.63..38,263.82 rows=98 width=51) (actual time=418.210..7,656.460 rows=592,896 loops=1)

5. 56.576 6,457.366 ↓ 5,933.8 593,379 1

Nested Loop Left Join (cost=6,979.35..38,227.94 rows=100 width=55) (actual time=418.181..6,457.366 rows=593,379 loops=1)

  • Filter: CASE WHEN pic.value THEN true WHEN (NOT pic.value) THEN false ELSE acl.has_permission_for_invoice(3, 8, i.id, i.id_client, i.id_supplier, i.online, i.accounted, i.accounted_partially, 'one_of_parents'::acl.direction) END
6. 204.911 4,620.653 ↓ 2,981.8 593,379 1

Hash Left Join (cost=6,978.78..36,475.75 rows=199 width=70) (actual time=418.110..4,620.653 rows=593,379 loops=1)

  • Hash Cond: (i.id_currency = currency.id)
7. 238.669 4,415.717 ↓ 2,981.8 593,379 1

Nested Loop Left Join (cost=6,976.86..36,473.25 rows=199 width=70) (actual time=418.065..4,415.717 rows=593,379 loops=1)

8. 260.953 3,583.669 ↓ 2,981.8 593,379 1

Nested Loop Left Join (cost=6,976.57..36,411.35 rows=199 width=70) (actual time=418.021..3,583.669 rows=593,379 loops=1)

9. 222.138 2,729.337 ↓ 2,981.8 593,379 1

Nested Loop (cost=6,976.29..36,351.63 rows=199 width=70) (actual time=417.943..2,729.337 rows=593,379 loops=1)

10. 217.217 1,320.441 ↓ 151.2 593,379 1

Hash Join (cost=6,975.86..34,583.87 rows=3,924 width=32) (actual time=417.911..1,320.441 rows=593,379 loops=1)

  • Hash Cond: (li.id_product = c2p.id_product)
11. 81.194 1,099.405 ↓ 15.1 455,353 1

Nested Loop (cost=6,774.60..33,817.09 rows=30,073 width=23) (actual time=414.027..1,099.405 rows=455,353 loops=1)

12. 267.296 618.742 ↓ 16.7 57,067 1

Hash Join (cost=6,774.17..20,803.28 rows=3,410 width=23) (actual time=413.977..618.742 rows=57,067 loops=1)

  • Hash Cond: (ins.id_invoice = i.id)
13. 85.993 85.993 ↓ 1.0 798,630 1

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

14. 69.641 265.453 ↓ 16.5 57,067 1

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

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3355kB
15. 195.812 195.812 ↓ 16.5 57,067 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.165..195.812 rows=57,067 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
16. 399.469 399.469 ↑ 12.1 8 57,067

Index Scan using id_invoice_position_unique on line_items li (cost=0.43..2.85 rows=97 width=8) (actual time=0.003..0.007 rows=8 loops=57,067)

  • Index Cond: (id_invoice_segment = ins.id)
17. 1.562 3.819 ↑ 1.0 5,244 1

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

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

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

  • Filter: (id_company = 64982)
  • Rows Removed by Filter: 2335
19. 1,186.758 1,186.758 ↑ 1.0 1 593,379

Index Scan using products_pkey on products p (cost=0.42..0.45 rows=1 width=46) (actual time=0.002..0.002 rows=1 loops=593,379)

  • Index Cond: (id = li.id_product)
20. 593.379 593.379 ↑ 1.0 1 593,379

Index Only Scan using units_pkey on units u (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=593,379)

  • Index Cond: (id = p.id_unit)
  • Heap Fetches: 140009
21. 593.379 593.379 ↑ 1.0 1 593,379

Index Only Scan using pk_companies on companies s (cost=0.29..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=593,379)

  • Index Cond: (id = p.id_supplier)
  • Heap Fetches: 36727
22. 0.012 0.025 ↑ 1.0 41 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.013 0.013 ↑ 1.0 41 1

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

24. 1,780.137 1,780.137 ↑ 1.0 1 593,379

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=593,379)

  • Index Cond: ((id_user = 8) AND (id_permission = 3) AND (id_invoice = i.id) AND (direction = 'one_of_parents'::acl.direction))
25. 1,186.758 1,186.758 ↑ 1.0 1 593,379

Index Scan using products2warehouses_id_company2product on products2warehouses p2w (cost=0.28..0.35 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=593,379)

  • Index Cond: (id_company2product = c2p.id)
  • Filter: (id_warehouse IS NOT NULL)
Planning time : 11.383 ms
Execution time : 8,146.442 ms