explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3cY7H

Settings
# exclusive inclusive rows x rows loops node
1. 3,585.369 103,798.059 ↓ 2.1 10,019,768 1

Hash Left Join (cost=11,175,180.24..12,263,756.01 rows=4,697,571 width=457) (actual time=65,673.778..103,798.059 rows=10,019,768 loops=1)

  • Hash Cond: ((first(mouvementswithpool.id_account)) = plan_comptable.auto_id)
2. 1,381.068 100,212.305 ↓ 2.1 10,019,768 1

Merge Left Join (cost=11,175,110.31..12,140,374.85 rows=4,697,571 width=435) (actual time=65,673.375..100,212.305 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice2.ecritures_id)
3. 2,319.515 77,834.143 ↓ 2.1 10,019,768 1

Merge Left Join (cost=9,648,698.88..10,600,202.73 rows=4,697,571 width=371) (actual time=44,676.279..77,834.143 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
4. 1,889.581 70,281.060 ↓ 2.1 10,019,768 1

Merge Left Join (cost=9,197,045.71..10,106,406.05 rows=4,697,571 width=339) (actual time=40,217.951..70,281.060 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
5. 2,801.327 63,923.164 ↓ 2.1 10,019,768 1

Merge Left Join (cost=8,996,005.40..9,823,707.42 rows=4,697,571 width=239) (actual time=39,679.108..63,923.164 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool.ecritures_id)
6. 3,799.496 12,322.845 ↑ 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,362.515..12,322.845 rows=4,697,489 loops=1)

  • Sort Key: ecrituresallinfo0digitfacture.id_ecritures
  • Sort Method: external merge Disk: 733176kB
7. 441.984 8,523.349 ↑ 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.035..8,523.349 rows=4,697,489 loops=1)

8. 8,081.365 8,081.365 ↑ 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.034..8,081.365 rows=4,697,489 loops=1)

9. 1,619.615 48,798.992 ↓ 3.9 10,006,584 1

Materialize (cost=7,836,038.36..8,607,824.55 rows=2,594,239 width=56) (actual time=28,316.587..48,798.992 rows=10,006,584 loops=1)

10. 9,511.298 47,179.377 ↓ 3.9 10,006,584 1

GroupAggregate (cost=7,836,038.36..8,575,396.56 rows=2,594,239 width=96) (actual time=28,316.585..47,179.377 rows=10,006,584 loops=1)

  • Group Key: mouvementswithpool.ecritures_id, mouvementswithpool.pool_name
11. 30,784.512 37,668.079 ↑ 1.0 25,909,859 1

Sort (cost=7,836,038.36..7,900,894.34 rows=25,942,393 width=316) (actual time=28,316.574..37,668.079 rows=25,909,859 loops=1)

  • Sort Key: mouvementswithpool.ecritures_id, mouvementswithpool.pool_name, mouvementswithpool.montant_absolu DESC, mouvementswithpool.montant_signe DESC
  • Sort Method: external merge Disk: 1470688kB
12. 6,883.567 6,883.567 ↑ 1.0 25,909,859 1

Seq Scan on mouvementswithpool (cost=0.00..828,564.25 rows=25,942,393 width=316) (actual time=320.946..6,883.567 rows=25,909,859 loops=1)

  • Filter: (pool_name IS DISTINCT FROM 'Accounts payable'::text)
  • Rows Removed by Filter: 1371121
13. 260.447 4,468.315 ↓ 1.9 2,098,550 1

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

14. 3,538.381 4,207.868 ↓ 1.2 1,311,931 1

GroupAggregate (cost=201,040.32..243,990.55 rows=1,078,566 width=144) (actual time=538.839..4,207.868 rows=1,311,931 loops=1)

  • Group Key: mouvementswithpool_1.ecritures_id, mouvementswithpool_1.pool_name
15. 320.153 669.487 ↓ 1.0 1,371,121 1

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

  • Sort Key: mouvementswithpool_1.ecritures_id
  • Sort Method: quicksort Memory: 156271kB
16. 349.334 349.334 ↓ 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.032..349.334 rows=1,371,121 loops=1)

  • Index Cond: (pool_name = 'Accounts payable'::text)
17. 1,399.266 5,233.568 ↓ 3.2 6,399,642 1

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

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217323kB
18. 178.045 3,834.302 ↓ 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,500.576..3,834.302 rows=2,539,058 loops=1)

19. 3,017.464 3,656.257 ↓ 1.3 2,539,058 1

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

  • Group Key: mouvementswithpool_2.ecritures_id, mouvementswithpool_2.pool_name
20. 638.793 638.793 ↓ 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..638.793 rows=3,507,276 loops=1)

  • Index Cond: (pool_name = 'VAT'::text)
21. 0.013 20,997.094 ↓ 0.0 0 1

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

  • Sort Key: gap_order_invoice2.ecritures_id
  • Sort Method: quicksort Memory: 25kB
22. 0.000 20,997.081 ↓ 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=20,997.081..20,997.081 rows=0 loops=1)

23. 18,750.656 20,997.081 ↓ 0.0 0 1

HashAggregate (cost=1,510,588.75..1,513,613.90 rows=134,451 width=100) (actual time=20,997.081..20,997.081 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: 4697489
24. 2,246.425 2,246.425 ↑ 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.011..2,246.425 rows=27,280,980 loops=1)

25. 0.175 0.385 ↑ 1.0 1,819 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
26. 0.210 0.210 ↑ 1.0 1,819 1

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

Planning time : 1.882 ms