explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 78L3

Settings
# exclusive inclusive rows x rows loops node
1. 2,017.382 124,563.996 ↓ 2.1 10,019,768 1

Merge Left Join (cost=8,909,411.29..9,170,102.76 rows=4,697,571 width=457) (actual time=109,347.206..124,563.996 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice2.ecritures_id)
2. 2,067.627 101,472.841 ↓ 2.1 10,019,768 1

Merge Left Join (cost=7,382,999.86..7,571,211.00 rows=4,697,571 width=393) (actual time=88,273.423..101,472.841 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
3. 1,794.023 94,334.922 ↓ 2.1 10,019,768 1

Merge Left Join (cost=6,931,346.69..7,077,414.33 rows=4,697,571 width=361) (actual time=83,835.818..94,334.922 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
4. 2,776.840 88,262.992 ↓ 2.1 10,019,768 1

Merge Left Join (cost=6,730,306.37..6,794,715.70 rows=4,697,571 width=261) (actual time=83,243.749..88,262.992 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool.ecritures_id)
5. 3,724.742 12,244.247 ↑ 1.0 4,697,489 1

Sort (cost=1,159,967.04..1,171,710.96 rows=4,697,571 width=187) (actual time=11,369.800..12,244.247 rows=4,697,489 loops=1)

  • Sort Key: ecrituresallinfo0digitfacture.id_ecritures
  • Sort Method: external merge Disk: 733,176kB
6. 461.272 8,519.505 ↑ 1.0 4,697,489 1

Subquery Scan on ecrituresallinfo0digitfacture (cost=0.00..205,877.35 rows=4,697,571 width=187) (actual time=0.041..8,519.505 rows=4,697,489 loops=1)

7. 8,058.233 8,058.233 ↑ 1.0 4,697,489 1

Seq Scan on ecrituresallinfo0 (cost=0.00..158,901.64 rows=4,697,571 width=187) (actual time=0.039..8,058.233 rows=4,697,489 loops=1)

8. 7,931.631 73,241.905 ↓ 3.7 10,006,584 1

Sort (cost=5,570,339.34..5,577,159.58 rows=2,728,098 width=78) (actual time=71,873.943..73,241.905 rows=10,006,584 loops=1)

  • Sort Key: mouvementswithpool.ecritures_id
  • Sort Method: external sort Disk: 683,280kB
9. 1,854.904 65,310.274 ↓ 3.7 10,006,584 1

Hash Left Join (cost=4,732,647.79..5,278,712.97 rows=2,728,098 width=78) (actual time=11,046.217..65,310.274 rows=10,006,584 loops=1)

  • Hash Cond: ((first(mouvementswithpool.id_account ORDER BY mouvementswithpool.montant_absolu DESC, mouvementswithpool.montant_signe DESC)) = plan_comptable.auto_id)
10. 46,052.106 63,454.979 ↓ 3.7 10,006,584 1

GroupAggregate (cost=4,732,577.86..5,213,850.72 rows=2,728,098 width=96) (actual time=11,045.815..63,454.979 rows=10,006,584 loops=1)

  • Group Key: mouvementswithpool.pool_name, mouvementswithpool.ecritures_id
11. 12,406.521 17,402.873 ↑ 1.0 25,909,859 1

Sort (cost=4,732,577.86..4,797,433.84 rows=25,942,393 width=36) (actual time=11,045.790..17,402.873 rows=25,909,859 loops=1)

  • Sort Key: mouvementswithpool.pool_name, mouvementswithpool.ecritures_id
  • Sort Method: external merge Disk: 1,303,640kB
12. 4,996.352 4,996.352 ↑ 1.0 25,909,859 1

Seq Scan on mouvementswithpool (cost=0.00..828,564.25 rows=25,942,393 width=36) (actual time=227.799..4,996.352 rows=25,909,859 loops=1)

  • Filter: (pool_name IS DISTINCT FROM 'Accounts payable'::text)
  • Rows Removed by Filter: 1,371,121
13. 0.198 0.391 ↑ 1.0 1,819 1

Hash (cost=47.19..47.19 rows=1,819 width=26) (actual time=0.391..0.391 rows=1,819 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 125kB
14. 0.193 0.193 ↑ 1.0 1,819 1

Seq Scan on plan_comptable (cost=0.00..47.19 rows=1,819 width=26) (actual time=0.009..0.193 rows=1,819 loops=1)

15. 286.476 4,277.907 ↓ 1.9 2,098,550 1

Materialize (cost=201,040.32..257,472.62 rows=1,078,566 width=104) (actual time=592.064..4,277.907 rows=2,098,550 loops=1)

16. 3,280.747 3,991.431 ↓ 1.2 1,311,931 1

GroupAggregate (cost=201,040.32..243,990.55 rows=1,078,566 width=144) (actual time=592.062..3,991.431 rows=1,311,931 loops=1)

  • Group Key: mouvementswithpool_1.ecritures_id, mouvementswithpool_1.pool_name
17. 325.873 710.684 ↓ 1.0 1,371,121 1

Sort (cost=201,040.32..204,386.79 rows=1,338,587 width=33) (actual time=592.028..710.684 rows=1,371,121 loops=1)

  • Sort Key: mouvementswithpool_1.ecritures_id
  • Sort Method: quicksort Memory: 156,271kB
18. 384.811 384.811 ↓ 1.0 1,371,121 1

Index Scan using test_index on mouvementswithpool mouvementswithpool_1 (cost=0.56..64,823.83 rows=1,338,587 width=33) (actual time=0.024..384.811 rows=1,371,121 loops=1)

  • Index Cond: (pool_name = 'Accounts payable'::text)
19. 1,248.789 5,070.292 ↓ 3.2 6,399,642 1

Sort (cost=451,653.16..456,719.76 rows=2,026,639 width=36) (actual time=4,437.601..5,070.292 rows=6,399,642 loops=1)

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217,323kB
20. 171.449 3,821.503 ↓ 1.3 2,539,058 1

Subquery Scan on entriessumpooltva (cost=193,756.69..239,356.07 rows=2,026,639 width=36) (actual time=2,523.406..3,821.503 rows=2,539,058 loops=1)

21. 2,993.476 3,650.054 ↓ 1.3 2,539,058 1

HashAggregate (cost=193,756.69..219,089.67 rows=2,026,639 width=44) (actual time=2,523.405..3,650.054 rows=2,539,058 loops=1)

  • Group Key: mouvementswithpool_2.ecritures_id, mouvementswithpool_2.pool_name
22. 656.578 656.578 ↓ 1.0 3,507,276 1

Index Scan using test_index on mouvementswithpool mouvementswithpool_2 (cost=0.56..167,771.55 rows=3,464,685 width=18) (actual time=0.019..656.578 rows=3,507,276 loops=1)

  • Index Cond: (pool_name = 'VAT'::text)
23. 0.011 21,073.773 ↓ 0.0 0 1

Sort (cost=1,526,411.43..1,526,747.56 rows=134,451 width=68) (actual time=21,073.773..21,073.773 rows=0 loops=1)

  • Sort Key: gap_order_invoice2.ecritures_id
  • Sort Method: quicksort Memory: 25kB
24. 0.000 21,073.762 ↓ 0.0 0 1

Subquery Scan on gap_order_invoice2 (cost=1,510,588.75..1,514,958.41 rows=134,451 width=68) (actual time=21,073.762..21,073.762 rows=0 loops=1)

25. 18,871.219 21,073.762 ↓ 0.0 0 1

HashAggregate (cost=1,510,588.75..1,513,613.90 rows=134,451 width=100) (actual time=21,073.762..21,073.762 rows=0 loops=1)

  • Group Key: mouvementswithpool_3.ecritures_id
  • Filter: ((COALESCE(sum(CASE WHEN (mouvementswithpool_3.pool_name = 'Accounts payable'::text) THEN mouvementswithpool_3.montant_signe ELSE NULL::numeric END), '0'::numeric) IS DISTINCT FROM '0'::numeric) AND (COALESCE(sum(CASE WHEN (mouvementswithpool_3.pool_name = 'Suppliers - Accrued invoices'::text) THEN mouvementswithpool_3.montant_signe ELSE NULL::numeric END), '0'::numeric) IS DISTINCT FROM '0'::numeric) AND (COALESCE(sum(CASE WHEN (mouvementswithpool_3.pool_name = 'VAT'::text) THEN mouvementswithpool_3.montant_signe ELSE NULL::numeric END), '0'::numeric) = '0'::numeric))
  • Rows Removed by Filter: 4,697,489
26. 2,202.543 2,202.543 ↑ 1.0 27,280,980 1

Seq Scan on mouvementswithpool mouvementswithpool_3 (cost=0.00..760,361.80 rows=27,280,980 width=18) (actual time=0.010..2,202.543 rows=27,280,980 loops=1)