explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Js0L

Settings
# exclusive inclusive rows x rows loops node
1. 1,419.785 111,675.467 ↑ 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,167.101..111,675.467 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = gap_order_invoice2.ecritures_id)
2. 1,301.147 89,099.368 ↑ 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,010.780..89,099.368 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = entriessumpooltva.ecritures_autoid)
3. 2,144.486 82,587.234 ↑ 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,394.962..82,587.234 rows=4,697,489 loops=1)

  • Merge Cond: (ecrituresallinfo0digitfacture.id_ecritures = mouvementswithpool_1.ecritures_id)
4. 1,603.013 18,311.833 ↑ 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,394.906..18,311.833 rows=4,697,489 loops=1)

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

Sort (cost=1,159,967.04..1,171,710.96 rows=4,697,571 width=187) (actual time=10,922.959..12,036.104 rows=4,697,489 loops=1)

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

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

8. 215.032 4,672.716 ↓ 9.8 1,311,931 1

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

9. 3,768.110 4,457.684 ↓ 9.8 1,311,931 1

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

  • Group Key: mouvementswithpool.ecritures_id
10. 373.886 689.574 ↓ 1.0 1,371,121 1

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

  • Sort Key: mouvementswithpool.ecritures_id
  • Sort Method: quicksort Memory: 156131kB
11. 315.688 315.688 ↓ 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.026..315.688 rows=1,371,121 loops=1)

  • Index Cond: (pool_name = 'Accounts payable'::text)
12. 1,102.782 62,130.915 ↓ 34.8 4,684,305 1

Materialize (cost=0.72..3,025,397.85 rows=134,451 width=102) (actual time=0.054..62,130.915 rows=4,684,305 loops=1)

13. 2,227.372 61,028.133 ↓ 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.052..61,028.133 rows=4,684,305 loops=1)

14. 43,970.845 54,116.456 ↓ 34.8 4,684,305 1

GroupAggregate (cost=0.44..2,982,565.66 rows=134,451 width=112) (actual time=0.042..54,116.456 rows=4,684,305 loops=1)

  • Group Key: mouvementswithpool_1.ecritures_id
15. 10,145.611 10,145.611 ↑ 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.014..10,145.611 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,210.542 5,210.987 ↓ 18.9 2,539,058 1

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

  • Sort Key: entriessumpooltva.ecritures_autoid
  • Sort Method: quicksort Memory: 217323kB
18. 172.861 4,000.445 ↓ 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,717.463..4,000.445 rows=2,539,058 loops=1)

19. 3,154.495 3,827.584 ↓ 18.9 2,539,058 1

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

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

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

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

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

23. 18,918.208 21,156.302 ↓ 0.0 0 1

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

Planning time : 1.688 ms