explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WVcN

Settings
# exclusive inclusive rows x rows loops node
1. 1,483.389 122,704.670 ↑ 1.0 4,697,489 1

Merge Left Join (cost=4,511,584.12..7,688,965.61 rows=4,697,571 width=481) (actual time=46,400.772..122,704.670 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice2.ecritures_id)
2. 1,346.037 98,745.991 ↑ 1.0 4,697,489 1

Merge Left Join (cost=2,985,140.74..6,090,041.90 rows=4,697,571 width=417) (actual time=23,925.475..98,745.991 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
3. 2,189.875 88,340.181 ↑ 1.0 4,697,489 1

Merge Left Join (cost=2,124,763.71..5,215,904.18 rows=4,697,571 width=385) (actual time=15,541.658..88,340.181 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
4. 1,569.342 22,521.525 ↑ 1.0 4,697,489 1

Merge Left Join (cost=2,124,763.00..2,177,054.70 rows=4,697,571 width=287) (actual time=15,541.591..22,521.525 rows=4,697,489 loops=1)

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

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

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

8. 221.544 8,722.092 ↓ 9.8 1,311,931 1

Materialize (cost=964,795.96..991,919.23 rows=134,446 width=104) (actual time=4,414.112..8,722.092 rows=1,311,931 loops=1)

9. 3,887.462 8,500.548 ↓ 9.8 1,311,931 1

GroupAggregate (cost=964,795.96..990,238.66 rows=134,446 width=136) (actual time=4,414.108..8,500.548 rows=1,311,931 loops=1)

  • Group Key: mouvementswithpool.ecritures_id
10. 359.815 4,613.086 ↓ 1.0 1,371,121 1

Sort (cost=964,795.96..968,142.53 rows=1,338,629 width=25) (actual time=4,414.081..4,613.086 rows=1,371,121 loops=1)

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

Seq Scan on mouvementswithpool (cost=0.00..828,574.90 rows=1,338,629 width=25) (actual time=0.021..4,253.271 rows=1,371,121 loops=1)

  • Filter: (pool_name = 'Accounts payable'::text)
  • Rows Removed by Filter: 25909859
12. 1,102.206 63,628.781 ↓ 34.8 4,684,305 1

Materialize (cost=0.72..3,025,424.91 rows=134,451 width=102) (actual time=0.065..63,628.781 rows=4,684,305 loops=1)

13. 2,181.951 62,526.575 ↓ 34.8 4,684,305 1

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

14. 45,014.527 55,660.319 ↓ 34.8 4,684,305 1

GroupAggregate (cost=0.44..2,982,592.72 rows=134,451 width=112) (actual time=0.054..55,660.319 rows=4,684,305 loops=1)

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

Index Scan using mvmntswthpl_crtrs_d_ndx on mouvementswithpool mouvementswithpool_1 (cost=0.44..2,592,100.17 rows=25,943,203 width=36) (actual time=0.014..10,645.792 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,334.567 9,059.773 ↓ 18.9 2,539,058 1

Sort (cost=860,377.03..860,713.16 rows=134,451 width=36) (actual time=8,383.812..9,059.773 rows=2,539,058 loops=1)

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217323kB
18. 183.025 7,725.206 ↓ 18.9 2,539,058 1

Subquery Scan on entriessumpooltva (cost=845,898.87..848,924.01 rows=134,451 width=36) (actual time=6,211.696..7,725.206 rows=2,539,058 loops=1)

19. 3,224.331 7,542.181 ↓ 18.9 2,539,058 1

HashAggregate (cost=845,898.87..847,579.50 rows=134,451 width=36) (actual time=6,211.695..7,542.181 rows=2,539,058 loops=1)

  • Group Key: mouvementswithpool_2.ecritures_id
20. 4,317.850 4,317.850 ↓ 1.0 3,507,276 1

Seq Scan on mouvementswithpool mouvementswithpool_2 (cost=0.00..828,574.90 rows=3,464,793 width=10) (actual time=3,574.245..4,317.850 rows=3,507,276 loops=1)

  • Filter: (pool_name = 'VAT'::text)
  • Rows Removed by Filter: 23773704
21. 0.013 22,475.290 ↓ 0.0 0 1

Sort (cost=1,526,443.38..1,526,779.51 rows=134,451 width=68) (actual time=22,475.290..22,475.290 rows=0 loops=1)

  • Sort Key: gap_order_invoice2.ecritures_id
  • Sort Method: quicksort Memory: 25kB
22. 0.000 22,475.277 ↓ 0.0 0 1

Subquery Scan on gap_order_invoice2 (cost=1,510,620.70..1,514,990.36 rows=134,451 width=68) (actual time=22,475.277..22,475.277 rows=0 loops=1)

23. 20,130.654 22,475.277 ↓ 0.0 0 1

HashAggregate (cost=1,510,620.70..1,513,645.85 rows=134,451 width=100) (actual time=22,475.277..22,475.277 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,344.623 2,344.623 ↑ 1.0 27,280,980 1

Seq Scan on mouvementswithpool mouvementswithpool_3 (cost=0.00..760,370.32 rows=27,281,832 width=18) (actual time=0.011..2,344.623 rows=27,280,980 loops=1)