explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aolk

Settings
# exclusive inclusive rows x rows loops node
1. 1,453.625 104,385.594 ↓ 2.1 10,019,768 1

Merge Left Join (cost=10,726,116.44..14,240,827.45 rows=4,697,571 width=457) (actual time=73,478.001..104,385.594 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice.ecritures_id)
2. 2,020.310 86,857.396 ↓ 2.1 10,019,768 1

Merge Left Join (cost=10,726,115.88..10,912,319.14 rows=4,697,571 width=393) (actual time=73,477.934..86,857.396 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
3. 1,832.302 79,677.053 ↓ 2.1 10,019,768 1

Merge Left Join (cost=10,274,462.71..10,418,522.46 rows=4,697,571 width=361) (actual time=69,008.817..79,677.053 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
4. 2,760.458 73,525.997 ↓ 2.1 10,019,768 1

Merge Left Join (cost=10,073,422.39..10,135,823.83 rows=4,697,571 width=261) (actual time=68,457.056..73,525.997 rows=10,019,768 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool.ecritures_id)
5. 3,681.758 11,972.892 ↑ 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,058.333..11,972.892 rows=4,697,489 loops=1)

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

7. 7,862.486 7,862.486 ↑ 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.038..7,862.486 rows=4,697,489 loops=1)

8. 10,141.047 58,792.647 ↓ 3.9 10,006,584 1

Sort (cost=8,913,455.36..8,919,940.95 rows=2,594,239 width=78) (actual time=57,398.714..58,792.647 rows=10,006,584 loops=1)

  • Sort Key: mouvementswithpool.ecritures_id
  • Sort Method: external sort Disk: 683280kB
9. 1,839.068 48,651.600 ↓ 3.9 10,006,584 1

Hash Left Join (cost=8,549,524.10..8,637,079.66 rows=2,594,239 width=78) (actual time=41,818.685..48,651.600 rows=10,006,584 loops=1)

  • Hash Cond: ((first(mouvementswithpool.id_account)) = plan_comptable.auto_id)
10. 17,974.093 46,812.141 ↓ 3.9 10,006,584 1

HashAggregate (cost=8,549,454.17..8,575,396.56 rows=2,594,239 width=96) (actual time=41,818.280..46,812.141 rows=10,006,584 loops=1)

  • Group Key: mouvementswithpool.ecritures_id, mouvementswithpool.pool_name
11. 22,449.619 28,838.048 ↑ 1.0 25,909,859 1

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

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

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

  • Filter: (pool_name IS DISTINCT FROM 'Accounts payable'::text)
  • Rows Removed by Filter: 1371121
13. 0.194 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: 2048 Batches: 1 Memory Usage: 125kB
14. 0.197 0.197 ↑ 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.197 rows=1,819 loops=1)

15. 284.292 4,318.754 ↓ 1.9 2,098,550 1

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

16. 3,345.054 4,034.462 ↓ 1.2 1,311,931 1

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

  • Group Key: mouvementswithpool_1.ecritures_id, mouvementswithpool_1.pool_name
17. 338.142 689.408 ↓ 1.0 1,371,121 1

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

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

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

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

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217323kB
20. 175.718 3,850.569 ↓ 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,525.243..3,850.569 rows=2,539,058 loops=1)

21. 3,027.481 3,674.851 ↓ 1.3 2,539,058 1

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

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

  • Index Cond: (pool_name = 'VAT'::text)
23. 41.629 16,074.573 ↑ 10.4 263,432 1

Materialize (cost=0.56..3,282,663.16 rows=2,728,098 width=68) (actual time=0.065..16,074.573 rows=263,432 loops=1)

24. 177.190 16,032.944 ↑ 11.1 246,593 1

Subquery Scan on gap_order_invoice (cost=0.56..3,275,842.92 rows=2,728,098 width=68) (actual time=0.061..16,032.944 rows=246,593 loops=1)

25. 9,816.948 15,855.754 ↑ 11.1 246,593 1

GroupAggregate (cost=0.56..3,180,359.49 rows=2,728,098 width=44) (actual time=0.054..15,855.754 rows=246,593 loops=1)

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

Index Scan using text_index2 on mouvementswithpool mouvementswithpool_3 (cost=0.56..2,716,582.83 rows=27,280,980 width=18) (actual time=0.016..6,038.806 rows=27,280,980 loops=1)

Planning time : 1.889 ms