explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YUiT

Settings
# exclusive inclusive rows x rows loops node
1. 1,378.577 113,417.074 ↑ 1.0 4,697,489 1

Merge Left Join (cost=3,086,992.64..6,264,346.33 rows=4,697,571 width=481) (actual time=37,791.868..113,417.074 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice2.ecritures_id)
2. 1,295.137 90,589.958 ↑ 1.0 4,697,489 1

Merge Left Join (cost=1,560,581.21..4,665,454.58 rows=4,697,571 width=417) (actual time=16,343.321..90,589.958 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
3. 2,098.127 83,946.550 ↑ 1.0 4,697,489 1

Merge Left Join (cost=1,361,008.07..4,452,120.74 rows=4,697,571 width=385) (actual time=11,557.636..83,946.550 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
4. 1,558.083 18,550.937 ↑ 1.0 4,697,489 1

Merge Left Join (cost=1,361,007.35..1,413,298.32 rows=4,697,571 width=287) (actual time=11,557.582..18,550.937 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool.ecritures_id)
5. 3,950.384 12,174.499 ↑ 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,006.971..12,174.499 rows=4,697,489 loops=1)

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

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

8. 211.012 4,818.355 ↓ 9.8 1,311,931 1

Materialize (cost=201,040.32..228,162.86 rows=134,446 width=104) (actual time=550.607..4,818.355 rows=1,311,931 loops=1)

9. 3,860.478 4,607.343 ↓ 9.8 1,311,931 1

GroupAggregate (cost=201,040.32..226,482.28 rows=134,446 width=136) (actual time=550.604..4,607.343 rows=1,311,931 loops=1)

  • Group Key: mouvementswithpool.ecritures_id
10. 363.287 746.865 ↓ 1.0 1,371,121 1

Sort (cost=201,040.32..204,386.79 rows=1,338,587 width=25) (actual time=550.574..746.865 rows=1,371,121 loops=1)

  • Sort Key: mouvementswithpool.ecritures_id
  • Sort Method: quicksort Memory: 156131kB
11. 383.578 383.578 ↓ 1.0 1,371,121 1

Index Scan using test_index on mouvementswithpool (cost=0.56..64,823.83 rows=1,338,587 width=25) (actual time=0.061..383.578 rows=1,371,121 loops=1)

  • Index Cond: (pool_name = 'Accounts payable'::text)
12. 1,102.856 63,297.486 ↓ 34.8 4,684,305 1

Materialize (cost=0.72..3,025,397.85 rows=134,451 width=102) (actual time=0.052..63,297.486 rows=4,684,305 loops=1)

13. 2,149.415 62,194.630 ↓ 34.8 4,684,305 1

Nested Loop Left Join (cost=0.72..3,025,061.73 rows=134,451 width=102) (actual time=0.051..62,194.630 rows=4,684,305 loops=1)

14. 45,004.363 55,360.910 ↓ 34.8 4,684,305 1

GroupAggregate (cost=0.44..2,982,565.66 rows=134,451 width=112) (actual time=0.041..55,360.910 rows=4,684,305 loops=1)

  • Group Key: mouvementswithpool_1.ecritures_id
15. 10,356.547 10,356.547 ↑ 1.0 25,909,859 1

Index Scan using mvmntswthpl_crtrs_d_ndx on mouvementswithpool mouvementswithpool_1 (cost=0.44..2,592,085.26 rows=25,942,393 width=36) (actual time=0.013..10,356.547 rows=25,909,859 loops=1)

  • Filter: (pool_name IS DISTINCT FROM 'Accounts payable'::text)
  • Rows Removed by Filter: 1371121
16. 4,684.305 4,684.305 ↑ 1.0 1 4,684,305

Index Scan using pk_plan_comptable on plan_comptable (cost=0.28..0.30 rows=1 width=26) (actual time=0.001..0.001 rows=1 loops=4,684,305)

  • Index Cond: ((first(mouvementswithpool_1.id_account ORDER BY mouvementswithpool_1.montant_absolu DESC, mouvementswithpool_1.montant_signe DESC)) = auto_id)
17. 1,271.088 5,348.271 ↓ 18.9 2,539,058 1

Sort (cost=199,573.14..199,909.27 rows=134,451 width=36) (actual time=4,785.680..5,348.271 rows=2,539,058 loops=1)

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217323kB
18. 175.741 4,077.183 ↓ 18.9 2,539,058 1

Subquery Scan on entriessumpooltva (cost=185,094.98..188,120.12 rows=134,451 width=36) (actual time=2,779.646..4,077.183 rows=2,539,058 loops=1)

19. 3,173.141 3,901.442 ↓ 18.9 2,539,058 1

HashAggregate (cost=185,094.98..186,775.61 rows=134,451 width=36) (actual time=2,779.646..3,901.442 rows=2,539,058 loops=1)

  • Group Key: mouvementswithpool_2.ecritures_id
20. 728.301 728.301 ↓ 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=10) (actual time=0.036..728.301 rows=3,507,276 loops=1)

  • Index Cond: (pool_name = 'VAT'::text)
21. 0.010 21,448.539 ↓ 0.0 0 1

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

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

23. 19,218.080 21,448.528 ↓ 0.0 0 1

HashAggregate (cost=1,510,588.75..1,513,613.90 rows=134,451 width=100) (actual time=21,448.528..21,448.528 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,230.448 2,230.448 ↑ 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,230.448 rows=27,280,980 loops=1)

Planning time : 1.810 ms